AFTER triggers

• Definition and properties

  • Performs a set of actions when fired
  • The actions are performed only after the DML event is finisehd
  • Used with INSERT, UPDATE, and DELETE statements for tables or views

• AFTER trigger prerequisites

  • Table or view needed for DML statements
  • The trigger will be attached to the same table Output from table Products used for this example:

  

• AFTER trigger definition

        CREATE TRIGGER TrackRetiredProducts
        ON Products
        AFTER DELETE
        AS
            INSERT INTO RetiredProducts (Product, Measure)
            SELECT Product, Measure
            FROM deleted;
  

• “inserted” and “deleted” tables

  • Special Tables are used by DML triggers
  • Created automatically by SQL Server

  Special Table	INSERT	UPDATE	DELETE
  inserted	new rows	new rows	N/A
  deleted	N/A	updated rows	removed rows

• The complete AFTER trigger

        CREATE TRIGGER TrackRetiredProducts
        ON Products
        AFTER DELETE
        AS 
            INSERT INTO RetiredProducts (Product, Measure)
            SELECT Product, Measure
            FROM deleted;
  

---

INSTEAD OF triggers(DML)

• Definition and Properties

  • Performs a set of actions when fired
  • The actions are performed instead of the DML event
  • The DML event does not run anymore
  • Used with INSERT, UPDATE, and DELETE statements for table or views

• INSTEAD OF trigger definition

        CREATE TRIGGER PreventOrdersUpdate
        ON Orders
        INSTEAD OF UPDATE
        AS  
            RAISERROR('Updates on "Orders" table are not permitted. Plae a new order to add new products', 16, 1);
  

---

DDL Triggers

• Definition and Properties

  | DML triggers | DDL triggers | | events associated with DML statements INSERT, UPDATE, DELETE | events associated with DDL statements CREATE, ALTER, DROP | | used with AFTER or INSTEAD OF | only used with AFTER | | attached to tables or views | attached to database or servers | | inserted and deleted special tables | no special tables |

• DDl trigger definition

        CREATE TRIGGER TrackTableChanges
        ON DATABASE
        FOR CREATE_TABLE,
            ALTER_TABLE,
            DROP_TABLE
        AS 
            INSERT INTO TablesChangeLog(EventData, ChangedBy)
            VALUES (EVENTDATA(), USER);
  

• Preventing the triggering events for DML triggers

        CREATE TRIGGER PreventTableDeletion
        ON DATABASE
        FOR DROP_TABLE
        AS 
            RAISERROR('You are not allowed to remove tables from this database.', 16, 1);
            ROLLBACK:
  

---

Logon Triggers

• Definition and Properties

  • Performs a set of actions when fired
  • The actions are performed for LOGON events
  • After authentication phase, but before the session establishment

• Logon trigger definition

        CREATE TRIGGER LogonAUdit
        ON ALL SERVER WITH EXECUTE AS 'sa'
        FOR LOGON
        AS 
            INSERT INTO ServerLogonLog
                (LoginName, LoginDate, SessionId, SourceIPAddress)
            SELECT ORIGINAL_LOGIN(), GETDATE(), @@SPID, client_net_address
            FROM SYS.DM_EXEC_CONNECTIONS WHERE  session_id = @@SPID;
  

• Logon trigger definition summary

        CREATE TRIGGER LogonAudit
        ON ALL SERVER WITH EXECUTE AS 'sa'
        FOR LOGON
        AS 
            INSERT INTO ServerLogonLog
                (LoginName, LoginDate, SessionID, SourceIPAddress)
            SELECT ORIGINAL_LOGIN(), GETDATE(), @@SPID, client_net_address
            FROM SYS.DM_EXEC_CONNECTIONS WHERE session_id = @@SPID;