CODEWITHSUNDEEP
sqlsql-servertriggers
Beginnerdevelopper
Beginnerdevelopper

Reputation: 13

Trigger for allow delete view

I want to create a trigger which allows to make deletions in a view. The modification relates multiples tables and I have an error when I try to delete a line of my view.

Here are my tables : 

create table Etudiant 
(
    mat int,
    nom char(25),
    prenom char(25),
    datenais datetime
)

create table Cours 
(
    ccode int, 
    nomcours char(25),
    enseignant char(25),
    credits char(255)
)

create table Examen 
(
    mat int,
    ccode int,
    dateExa datetime, 
    note char(25)
)

My view :

create view SYNT_EXAMEN (mat, nom, prenom, ccode, dateExa, note)
as 
      select etudiant.mat, nom, prenom, cours.ccode, dateExa, note 
      from etudiant 
      join examen on etudiant.mat = examen.mat
      join cours on cours.ccode = examen.ccode

My trigger :

CREATE TRIGGER declencheur_suppression
ON synt_examen
INSTEAD OF DELETE
AS 
BEGIN
    DELETE FROM synt_examen 
        SELECT nom FROM inserted i
END

When I execute my trigger I want to delete a line (for example delete from synt_examen where nom = 'dupont') of my view without error :

"The view or function 'synt_examen' can not be updated because the change affects more than one base table."

Upvotes: 1

Views: 2203

Answers (2)

Fandango68
Fandango68

Reputation: 4888

An alternative to Zohar's answer, is to not delete records (for auditing purposes), but to flag the record deleted, and change your view to reflect this.

i.e.

Create the trigger as per the above, but alter it into an UPDATE statement...

USE [yourdatabase];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TRIGGER declencheur_suppression
ON synt_examen
INSTEAD OF DELETE
AS 
   UPDATE t
   SET t.Status = 0
   FROM Etudiant AS t
      INNER JOIN deleted d ON t.ID = d.ID
GO

Of course you will need to add another column called 'Status' and default it to 1 for ACTIVE (or non deleted). When it's 0, it implies it's been deleted.

To reflect that, change your view to have a WHERE clause such as WHERE Etudiant = 1.

I prefer this method because physically deleting records should be avoided as much as possible, for good DBA practice.

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82504

The point of an instead of delete trigger on the view is to delete the data from the tables on which the view relies on. You can't delete directly from the view which is why you must use an instead of delete trigger.

But this doesn't mean that in the trigger itself you can delete from the view - that wouldn't make sense. Instead, in the trigger you need to instruct SQL Server what do you want to delete from where - you do that by constructing a delete statement for each table involved you want to delete from. You can specify multiple delete statements in your trigger if you want to delete from multiple tables.

Based on the code you posted, I think you are looking for something like this:

CREATE TRIGGER declencheur_suppression
ON synt_examen
INSTEAD OF DELETE
AS 
BEGIN
    DELETE t
    FROM Etudiant t
    JOIN deleted d ON t.nom = d.nom
END

This trigger will delete records from the Etudiant if you deleted them from the view, based on the nom column.

Upvotes: 1

Related Questions