Why and when to use a trigger:
We use a trigger when we want some event to happen automatically on certain desirable scenarios. You have a table that changes frequently, now you want to know how many times and when these changes take place. In that case you can create a trigger that will insert the desired data into another table whenever any change in the main table occurs.
In SQL Server we can create the following 3 types of triggers:
• Data Definition Language (DDL) triggers
• Data Manipulation Language (DML) triggers
• Logon triggers
Example:
CREATE TRIGGER trgAfterInsert ON[dbo].[Employee_Test]
FOR INSERT
AS
declare@ empid int; declare@ empname varchar(100);
declare@ empsal decimal(10, 2);
declare@ audit_action varchar(100);
select@ empid = i.Emp_ID from inserted i;
select@ empname = i.Emp_Name from inserted i;
select@ empsal = i.Emp_Sal from inserted i;
set@ audit_action = 'Inserted Record -- After Insert Trigger.';
insert into Employee_Test_Audit (Emp_ID, Emp_Name, Emp_Sal, Audit_Action, Audit_Timestamp) values(@empid, @empname, @empsal, @audit_action, getdate());
PRINT 'AFTER INSERT trigger fired.'
GO
