Trigger is a code just like a stored procedure that runs whenever an event takes place. The event is related to DML (Data manipulation Language ) events and DDL(Data definition Language ) events .
1 – DDL(Data definition Language ) Triggers :
DDL statements means statements like :Create , alter etc . These triggers can be used for Auditing purposes or change management . They can be used for tracking whenever a CREATE table or ALTER table statement is being run and can help the database administrators to use the logged information through these triggers about the events of Creation , alter or other DDL events.
2 – DML(Data Manipulation Language ) Triggers :
SQL supports two types of DML triggers : After and instead of.
2.1 – AFTER Trigger :
After Trigger fires after the event it is associated with it completes.
This trigger works only on permanent tables.
In the trigger’s code you can access the table that are called inserted and deleted that contains which were affected by the modification that caused the trigger to fire. The inserted table contains the image of the new data inserted . Whereas the deleted table holds the old image of the affected rows.
Example : IF OBJECT_ID('dbo.Table1_Audit', 'U') IS NOT NULL DROP TABLE dbo.Table1_Audit; IF OBJECT_ID('dbo.Table1, 'U') IS NOT NULL DROP TABLE dbo.Table1; CREATE TABLE dbo.Table1 ( keycol INT NOT NULL PRIMARY KEY, datacol VARCHAR(10) NOT NULL ); CREATE TABLE dbo.Table1_Audit ( audit_lsn INT NOT NULL IDENTITY PRIMARY KEY, dt DATETIME NOT NULL DEFAULT(SYSDATETIME()), login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()), keycol INT NOT NULL, datacol VARCHAR(10) NOT NULL );
--Now creating trigger CREATE TRIGGER trg_Table1_insert_audit ON dbo.Table1 AFTER INSERT AS SET NOCOUNT ON; INSERT INTO dbo.Table1_Audit(keycol, datacol) SELECT keycol, datacol FROM inserted; GO
--Now insert values into the table1 INSERT INTO dbo.Table1(keycol, datacol) VALUES(1, 'a');
Once the insert statement is run the trigger trg_Table1_insert_audit runs . We can now verify the audit table1 :
SELECT audit_lsn, dt, login_name, keycol, datacol FROM dbo.Table1_Audit;
Now check the table1 :
select * from Table1
2.2 – INSTEAD OF Trigger :
In the Instead of triggers, the inserted and deleted tables contain the rows that were supposed or about to be affected (Inserted or Deleted) by the modification that caused the trigger to fire.
Example :
IF OBJECT_ID('dbo.Table1_Audit', 'U') IS NOT NULL DROP TABLE dbo.Table1_Audit;
IF OBJECT_ID('dbo.Table1, 'U') IS NOT NULL DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Table1_Audit
(
audit_lsn INT NOT NULL IDENTITY PRIMARY KEY,
dt DATETIME NOT NULL DEFAULT(SYSDATETIME()),
login_name sysname NOT NULL DEFAULT(ORIGINAL_LOGIN()),
keycol INT NOT NULL,
datacol VARCHAR(10) NOT NULL
);
--Now creating trigger INSTEAD OF CREATE TRIGGER trg_Table1_insert_audit ON dbo.Table1 instead of insert AS SET NOCOUNT ON; INSERT INTO dbo.Table1_Audit(keycol, datacol) SELECT keycol, datacol FROM inserted; GO
–Now insert values into the table1
INSERT INTO dbo.Table1(keycol, datacol) VALUES(1, 'a');
Once the insert statement is run the trigger trg_Table1_insert_audit runs . We can now verify the audit table1 :
SELECT audit_lsn, dt, login_name, keycol, datacol FROM dbo.Table1_Audit;
To make clear the difference , check the Table1 for understanding !
select * from Table1
SqlBird 