CODEWITHSUNDEEP
sql-servert-sql
Jonathan Durda
Jonathan Durda

Reputation: 41

SQL trigger on update or delete

I have to have one single trigger that fires on either the UPDATE OR DELETE operations. I have the trigger working fine for when one certain column is updated. However, I need different logic for when a DELETE operation was fired. How would I have both logic inside of one trigger? Here is what I have so far:

ALTER TRIGGER [dbo].[Audit_Emp_Trigger]
   ON  [dbo].[EMPLOYEE]
   AFTER UPDATE, DELETE
AS 

BEGIN
    --Only execute the trigger if the Dno field was updated or deleted
    IF UPDATE(Dno)
    BEGIN

    --If the Audit_Emp_Record table does not exist already, we need to create it
    IF OBJECT_ID('dbo.Audit_Emp_Record') IS NULL
    BEGIN
    --Table does not exist in database, so create table
    CREATE TABLE Audit_Emp_Record
    (
    date_of_change smalldatetime,
    old_Lname varchar (50),
    new_Lname varchar (50),
    old_ssn int,
    new_ssn int,
    old_dno int,
    new_dno int
    );

    --Once table is created, insert the values of the update operation into the table
    INSERT INTO Audit_Emp_Record(date_of_change, old_Lname, new_Lname, old_ssn, new_ssn, old_dno, new_dno) SELECT GETDATE(), D.Lname, I.Lname, D.Ssn, I.Ssn, D.Dno, I.Dno FROM inserted I JOIN deleted D ON I.Ssn = D.Ssn

    END

    ELSE
    BEGIN
    --The table already exists, so simply insert the new values of the update operation into the table
    INSERT INTO Audit_Emp_Record(date_of_change, old_Lname, new_Lname, old_ssn, new_ssn, old_dno, new_dno) SELECT GETDATE(), D.Lname, I.Lname, D.Ssn, I.Ssn, D.Dno, I.Dno FROM inserted I JOIN deleted D ON I.Ssn = D.Ssn
    END

    END

END

Upvotes: 2

Views: 5646

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48776

You can test for the type of operation by seeing which of the magic-/pseudo-tables -- INSERTED and DELETED have data in them. I prefer to use something like the following:

DECLARE @Operation CHAR(1);

IF (EXISTS(SELECT * FROM inserted))
BEGIN

   IF (EXISTS(SELECT * FROM deleted))
   BEGIN
      -- rows in both has to be an UPDATE
      SET @Operation = 'U';
   END;
   ELSE
   BEGIN
      -- no rows in "deleted" has to be an INSERT
      SET @Operation = 'I';
   END;

END;
ELSE
BEGIN
   -- no rows in "inserted" has to be a DELETE
   SET @Operation = 'D';
END;

You can then use the @Operation variable in an IF statement to do one or the other of those operations.

Something like:

IF (@Operation = 'U')
BEGIN

    --Only execute the trigger if the Dno field was updated or deleted
    IF UPDATE(Dno)
    BEGIN
       {your current code here}
    END;
END;
ELSE
BEGIN
    {what to do if the operation is a DELETE goes here}
END;

Technically you don't need the ELSE condition that sets @Operation = 'I';, but if you are going to copy/paste this code into various triggers or keep around as a template then no harm in it handling all three conditions.

Also, just as a side-note, you don't need the ELSE condition of the IF OBJECT_ID('dbo.Audit_Emp_Record') IS NULL statement, nor the INSERT INTO Audit_Emp_Record that is just after the CREATE TABLE but before the END. Just do the CREATE TABLE if it doesn't exist and then do the INSERT outside of that test. Meaning:

IF UPDATE(Dno)
BEGIN
    --If the Audit_Emp_Record table does not exist already, we need to create it
    IF OBJECT_ID('dbo.Audit_Emp_Record') IS NULL
    BEGIN
       --Table does not exist in database, so create table
       CREATE TABLE Audit_Emp_Record
       ...
    END

    INSERT INTO Audit_Emp_Record(...)
END

Upvotes: 6

Related Questions