Triggers

A trigger is a special type of stored procedure that executes in response to action on a table like insert, update and delete. It is a database object bound to a table and executes automatically when event occurs. We can’t invoke triggers explicitly.


Types of Triggers
 These are classified into two categories
 After Triggers or For Triggers
 Instead of Triggers

After Triggers
These will run after completion of any DML event like Insert, Update and Delete on a table. These after triggers will work only with tables not with VIEWS. After Insert, After Update and After Delete.

Magic Tables
 There are two temporary tables INSERTED, DELETED created by database engine automatically   when Insert / Delete occurs on a table.

 Syntax
 Create trigger trigger_name on Table_Name After / For (Insert /Delete/Update) As
Begin
Statements
 End
Examples of After Insert , After Update & After Delete Triggers

CREATE TRIGGER trg_items ON items FOR INSERT as
 BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM INSERTED

END

CREATE TRIGGER trg_updateitems ON items FOR UPDATE as
BEGIN
                 INSERT INTO Items_audit SELECT item_name, item_image, item_description FROM DELETED
 END 

Instead of Triggers 
These instead of triggers is used to perform any action before the event fires, we can create instead of triggers in tables and views, whereas we can create after triggers in views.

 Create trigger trigger_name on table / view instead of Insert / Update / Delete AS
 Begin
         Statements
 End

Example
 Create table tblInsert_Log (UserName varchar(250), description varchar(500), Action_Date datetime, SPID int)

Create trigger trg_trapInsert on Items instead of insert As
BEGIN
                 Insert into Items select Item_name,
                 Insert into tblInsert_Log VALUES (SUSER_NAME(),'Inserted New Record', GETDATE(), @@spid)
 END

DDL Triggers To Audit Server Level Events - SQL Server

In my earlier post I explained how to log DDL events, now we will see another example how to audit or log server level events.

CREATE TABLE DDL_ServerAudit_Log

(

       EventTime            DATETIME,

       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)

 

CREATE TRIGGER trg_AuditServerEvents ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN, GRANT_SERVER  AS

DECLARE @data XML
SET @data = EVENTDATA()
INSERT INTO kalyandb..DDL_ServerAudit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

To Verify 

USE master

CREATE LOGIN test WITH PASSWORD='Admin@123'
SELECT * FROM kalyandb..ddl_serveraudit_log


 Disable Trigger

DISABLE TRIGGER ALL ON DATABASE
GO
DISABLE TRIGGER ALL ON ALL SERVER

View triggers
select name from sys.triggers

select definition from sys.sql_modules where object_id = select object_id from sys.triggers where name = 'trigger_name')

Deleting triggers
drop trigger trigger_name on database


 

DDL Triggers to Audit DDL Events - SQL Server

DDL Triggers are not only to restrict however we can even audit the DDL changes. In regular triggers we have two magic tables called INSERTED, DELETED which helps to track the changes when any event fires. But to track the DDL changes we have a function EVENTDATA() which helps to track all DDL changes.

Below is the example to track all the DDL changes in the current database.

CREATE TABLE DDL_Audit_Log
(
       EventTime            DATETIME,
       LoginName            VARCHAR(250),
       UserName             VARCHAR(250),
       DatabaseName         VARCHAR(250),
       SchemaName           VARCHAR(250),
       ObjectName           VARCHAR(250),
       ObjectType           VARCHAR(250),
       DDLCommand           VARCHAR(MAX)
)
ALTER TRIGGER Trg_LogDDLEvents ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS AS
DECLARE @data XML
SET @data= EVENTDATA()
INSERT INTO DDL_Audit_Log VALUES
(       REPLACE(CONVERT(VARCHAR(50),@data.query('data(/EVENT_INSTANCE/PostTime)')),'T', ' '),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/LoginName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/UserName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/DatabaseName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/SchemaName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectName)')),
        CONVERT(VARCHAR(250), @data.query('data(/EVENT_INSTANCE/ObjectType)')),
        CONVERT(VARCHAR(MAX),@data.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
)

DDL Triggers - SQL Server

DDL Triggers are similar to regular triggers that fires in response to an event. Unlike DML Triggers they don’t fire in response to Update, Delete, and Insert statements on a table. They fire in response to DDL events like CREATE, ALTER, DROP etc.,
 

DDL Triggers helps to audit the database level changes or you can restrict to perform any kind of drop operation or to restrict to perform any kind of schema updates. DDL Triggers can fire in response to a DDL Event in the current database or in the current server.

 

Note : DDL Triggers do not fire in response to events that affect temporary tables.

 

The scope of the DDL Trigger is depends on the event, for example if we setup a Create Table trigger created to fire in response to a Create Table event that can be occurs in the database or in the server, if we take Create Login trigger then it will occur only at server level not database level.

 

Example 

USE kalyandb
GO
ALTER TRIGGER prevent_drop ON DATABASE FOR DROP_TABLE AS
 PRINT 'You must Disable the trigger "Prevent Drop" to drop this table'
 ROLLBACK
 

Viewing Triggers
 

SELECT * FROM SYS.triggers 

View Definition of a Trigger

SELECT DEFINITION FROM SYS.sql_modules WHERE OBJECT_ID = (SELECT OBJECT_ID FROM SYS.TRIGGERS WHERE NAME='PREVENT_DROP')
 

To Know the scope of trigger

 

SELECT type, name, parent_class_desc FROM sys.triggers

UNION

SELECT type, name, parent_class_desc FROM sys.server_triggers ;