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

User · Accepted Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  lt dml trigger option gt        n       FOR   AFTER   INSTEAD OF        INSERT           UPDATE           DELETE        NOT FOR REPLICATION    AS   sql statement              n     EXTERNAL NAME  lt method specifier        gt

[sql-server] insert/delete/update trigger in SQL server

Examples related to sql-server

Examples related to triggers