insert/delete/update trigger in SQL server

Question

I am trying to produce an all-in-one delete/insert/update trigger. I get two "incorrect syntax near AFTER at the second and third AFTERS and a syntax error near the last END.

CREATE TRIGGER trig_all_dml
 ON [dbo.file]
 AFTER UPDATE
 AS BEGIN
    UPDATE 
           (excess code)       
 END

 AFTER INSERT
 AS BEGIN
     UPDATE 
             (excess code)
  END

 AFTER DELETE
 AS BEGIN
    UPDATE (excess code)

  END
  GO

Hopefully, this is enough information. I think the problem is my syntax but I can't find the correct syntax online.

Why can't you just have three separate, focused triggers, one for each operation? Think about the Single Responsibility Principle! That'll make maintenance much easier down to road ... — marc_s
– marc_s, Commented Nov 26, 2013 at 6:06

Vishnu · Accepted Answer · 2014-03-12 13:55:30Z

87

the am giving you is the code for trigger for INSERT, UPDATE and DELETE this works fine on Microsoft SQL SERVER 2008 and onwards database i am using is Northwind

/* comment section first create a table to keep track of Insert, Delete, Update
create table Emp_Audit(
                    EmpID int,
                    Activity varchar(20),
                    DoneBy varchar(50),
                    Date_Time datetime NOT NULL DEFAULT GETDATE()
                   );

select * from Emp_Audit*/

create trigger Employee_trigger
on Employees
after UPDATE, INSERT, DELETE
as
declare @EmpID int,@user varchar(20), @activity varchar(20);
if exists(SELECT * from inserted) and exists (SELECT * from deleted)
begin
    SET @activity = 'UPDATE';
    SET @user = SYSTEM_USER;
    SELECT @EmpID = EmployeeID from inserted i;
    INSERT into Emp_Audit(EmpID,Activity, DoneBy) values (@EmpID,@activity,@user);
end

If exists (Select * from inserted) and not exists(Select * from deleted)
begin
    SET @activity = 'INSERT';
    SET @user = SYSTEM_USER;
    SELECT @EmpID = EmployeeID from inserted i;
    INSERT into Emp_Audit(EmpID,Activity, DoneBy) values(@EmpID,@activity,@user);
end

If exists(select * from deleted) and not exists(Select * from inserted)
begin 
    SET @activity = 'DELETE';
    SET @user = SYSTEM_USER;
    SELECT @EmpID = EmployeeID from deleted i;
    INSERT into Emp_Audit(EmpID,Activity, DoneBy) values(@EmpID,@activity,@user);
end

edited Mar 12, 2014 at 13:55

answered Mar 12, 2014 at 13:49

Vishnu

9027 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anita Mehta Over a year ago

Here U r doing trigger event(Update, delete, insert) i think on two table(inserted, deleted), but I want to do on one table(mytable), how can i do this?

Amirhossein Over a year ago

use Select 1 From inserted better than use * for performance.

Gordon Linoff Over a year ago

This code is broken because it assumes that inserted has only a single row. This should not be the accepted answer.

Ergin Çelik · Accepted Answer · 2017-09-06 08:04:27Z

12

I use that for all status (update, insert and delete)

CREATE TRIGGER trg_Insert_Test
ON [dbo].[MyTable]
AFTER UPDATE, INSERT, DELETE 
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Activity  NVARCHAR (50)

-- update
IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
BEGIN
    SET @Activity = 'UPDATE'
END

-- insert
IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
BEGIN
    SET @Activity = 'INSERT'
END

-- delete
IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
BEGIN
    SET @Activity = 'DELETE'
END



-- delete temp table
IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL DROP TABLE #tmpTbl

-- get last 1 row
SELECT * INTO #tmpTbl FROM (SELECT TOP 1 * FROM (SELECT * FROM inserted
                                                 UNION 
                                                 SELECT * FROM deleted
                                                 ) AS A ORDER BY A.Date DESC
                            ) AS T


-- try catch
BEGIN TRY 

    INSERT INTO MyTable  (
           [Code]
          ,[Name]
           .....
          ,[Activity])
    SELECT [Code]
          ,[Name]
          ,@Activity 
    FROM #tmpTbl

END TRY BEGIN CATCH END CATCH


-- delete temp table
IF OBJECT_ID('tempdb..#tmpTbl') IS NOT NULL DROP TABLE #tmpTbl

SET NOCOUNT OFF;
END

answered Sep 6, 2017 at 8:04

Ergin Çelik

7758 silver badges15 bronze badges

4 Comments

Allan F Over a year ago

Thanks !, interesting ... Does this work when multiple records are being updated, deleted in a single transaction / statement ? I will do some testing ! .. I am interested in a single trigger that fires for both update and delete regarding some audit / version record keeping ..

Ergin Çelik Over a year ago

I use this trigger for (insert, update and delete). It works well

Fandango68 Over a year ago

great little way of knowing what exactly triggered the trigger. Is it delete, update or an insert!? Thanks!

Paal Pedersen Over a year ago

thats a weird way of adding a change, by doing a union select one of either instered or deleted arbirtray by Date if it has a date column at all. if you select from deleted you are sure to save the old value. if your do two inserts for change you can save both old and new value.

D Stanley · Accepted Answer · 2017-07-11 13:33:23Z

11

Not possible, per MSDN:

You can have the same code execute for multiple trigger types, but the syntax does not allow for multiple code blocks in one trigger:

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

edited Jul 11, 2017 at 13:33

answered Nov 25, 2013 at 23:35

D Stanley

153k12 gold badges189 silver badges257 bronze badges

2 Comments

user963070 Over a year ago

I'm using "IF EXISTS(SELECT * FROM Inserted)", etc.

D Stanley Over a year ago

Sure you can do that - be aware that an update (from a trigger perspective) is treated like a DELETE followed by an INSERT - so the old values will be in DELETED and the updated values will be in INSERTED

Paul A. Trzyna · Accepted Answer · 2017-06-22 15:24:28Z

0

I agree with @Vishnu's answer. I would like to add that if you want to use the application user in your trigger you can use "context_info" to pass the info to the trigger.

I found following very helpful in doing that: http://jasondentler.com/blog/2010/01/exploiting-context_info-for-fun-and-audit

edited Jun 22, 2017 at 15:24

answered Oct 27, 2015 at 13:48

Paul A. Trzyna

3001 silver badge13 bronze badges

1 Comment

PhilipD Over a year ago

The URL above doesn't seem to work with the trailing "/". Try this instead: jasondentler.com/blog/2010/01/…

goktemkirez · Accepted Answer · 2022-05-28 17:13:07Z

0

I used Northwind DB and :

ALTER TRIGGER Update_StockQuantity ON dbo.[Order Details] 
AFTER INSERT, UPDATE, DELETE 
AS 
BEGIN 
    declare @productId int
    declare @quantity smallint
    declare @quantityBefore smallint

    --Read Table Before Update
    SELECT ProductID, ProductName, UnitsInStock FROM Products WHERE ProductID = @productId

    -- UPDATE
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
    BEGIN
        SELECT @productId=ProductID, @quantity=Quantity FROM inserted
        SELECT @quantityBefore=Quantity FROM deleted

        UPDATE Products SET UnitsInStock = UnitsInStock - @quantity + @quantityBefore WHERE ProductID = @productId
    END

    -- INSERT
    IF EXISTS (SELECT * FROM inserted) AND NOT EXISTS(SELECT * FROM deleted)
    BEGIN
        SELECT @productId=ProductID, @quantity=Quantity FROM inserted

        UPDATE Products SET UnitsInStock = UnitsInStock - @quantity WHERE ProductID = @productId
    END

    -- DELETE
    IF EXISTS (SELECT * FROM deleted) AND NOT EXISTS(SELECT * FROM inserted)
    BEGIN
        SELECT @productId=ProductID, @quantity=Quantity FROM deleted

        UPDATE Products SET UnitsInStock = UnitsInStock + @quantity WHERE ProductID = @productId
    END

    --Read Table After Update
    SELECT ProductID, ProductName, UnitsInStock FROM Products WHERE ProductID = @productId

END

answered May 28, 2022 at 17:13

goktemkirez

12 bronze badges

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

insert/delete/update trigger in SQL server

5 Answers 5

3 Comments

4 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

4 Comments

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related