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.

Answer 1

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 

Answer 2

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 [ ; ] > } 

Answer 3

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