Trigger

Definition: A special type of SPs which are defined to execute automatically when UPDATE/INSERT/DELETE statement is issued against a table/view
OR some DDL operation happens in database

Special tables
Inside DML trigger, we can use special tables like INSERTED or DELETED.
INSERTED table will contain the new value/record for particular table
DELETED table will contain the old value/record for particular table

Type of Triggers

• DML Trigger
• Before Trigger
• Getting executed before actual operation
• we can create N number of before triggers per table per action (IUD)
• Syntax
• CREATE TRIGGER trg ON TriggerCheck FOR INSERT -- Before
  AS
  BEGIN
        DECLARE @newValue VARCHAR(10)
       
        SELECT @newValue = tname FROM INSERTED
       
        PRINT @newValue
  END
• After Trigger
• Getting executed after actual operation
• we can create N number of before triggers per table per action (IUD)
• Syntax
• CREATE TRIGGER trg ON TriggerCheck AFTER INSERT
  AS
  BEGIN
        DECLARE @newValue VARCHAR(10)
       
        SELECT @newValue = tname FROM INSERTED
       
        PRINT @newValue
  END
• Instead of Trigger
• Getting executed instead of actual operation
• We can create only 1 instead of trigger per table per action (IUD)
• Syntax
• CREATE TRIGGER trg ON TriggerCheck INSTEAD OF INSERT
  AS
  BEGIN
        PRINT 'Not allowed'
  END
• DDL Trigger
• Introduced in 2005
• we can track DDL activities like CREATE, DROP, ALTER table/view/sp at database level or CREATE, DROP database/login at server level
• useful in following areas
• Auditing
• Maintaining coding standards for table/sp/function name
• For alert whenever some DDL activities happen at database level
• EVENTDATA is a special function which can be used inside trigger and returns XML data which can be further used for tracking
• let's say we want to track DDL activities within database then first we can create one customized table like this
• CREATE TABLE tblDDLEventLog
  (
      ID int IDENTITY(1,1) NOT NULL,
      EventTime datetime,
      EventType varchar(15),
      ServerName varchar(25),
      DatabaseName varchar(25),
      ObjectType varchar(25),
      ObjectName varchar(25),
      UserName varchar(15),
      CommandText varchar(max)
  )
• Now, create trigger to capture create table events
• CREATE TRIGGER ddltrg_CREATE_TABLE_LOG ON DATABASE -- Create Database DDL Trigger
  FOR CREATE_TABLE -- Trigger will raise when creating a Table
  AS
      DECLARE @xmlEventData XML
      -- Capture the event data that is created
      SET @xmlEventData = eventdata()
      -- Insert information to a EventLog table
      INSERT INTO tblDDLEventLog
      (
          EventTime,
          EventType,
          ServerName,
          DatabaseName,
          ObjectType,
          ObjectName,
          UserName,
          CommandText
      )
      SELECT  REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
              CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/EventType)')),
              CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ServerName)')),
              CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/DatabaseName)')),
              CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectType)')),
              CONVERT(VARCHAR(25), @xmlEventData.query('data(/EVENT_INSTANCE/ObjectName)')),
              CONVERT(VARCHAR(15), @xmlEventData.query('data(/EVENT_INSTANCE/UserName)')),
              CONVERT(VARCHAR(MAX), @xmlEventData.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
  GO
• Now create some table and check the entries in tblDDLEventLog table.

Something Extra

• Debugging of DML Trigger is difficult
• Generally DML triggers should be avoided and rather the same logic can be implemented at SP level
• Auditing of data can be handled by CDC
• If multiple BEFORE or AFTER triggers are available for single DML operation, then order will be same as it got created. However, sp_settriggerorder can be used to set the first and last order of trigger execution