SQL Trigger Why to use with Example

Introduction :

In SQL Trigger are main three type here we are explain each trigger with detail and with practically

step by step using database.

What is SQL Trigger :

Trigger is special kind of Store Procedure which is automatical response when the operation is perform on the sql database table like insertion ,updation and deletion operation. trigger is also database object which automatical invoke when table update.

Types Of SQL Trigger :

There are basically three types of trigger

* DML Trigger like INSERT UPDATE DELETE

* DDL Trigger CREATE or ALTER

* Database events, such as logon/logoff

DML Trigger :

DML means data manipulation language there are also two type after trigger or for trigger & Instead of trigger

(1) After or for Trigger :

After trigger are also divided After Insert ,After Update and After Deleted Trigger here we are explain all tree trigger with complete database with step by step using SQL Server 2008 R2

First Create database and add table with name tbl_employee

Now you need to create another table because when above three events are fire it can change database record and it record are store in another table Like tbl_Audit . in this table not need to asign primary key and auto increment identity.

Now, we create After insert trigger as below step
click on tbl_employee and then click on create new trigger

CREATE TRIGGER [dbo].[after_insert]
   ON [dbo].[tbl_employee]
   AFTER INSERT
AS
BEGIN
    declare @ID int;
    declare @Name nvarchar(100);
    declare @City nvarchar(100);
    declare @Income decimal(10,2);
    declare @Action varchar(100);

    select @ID=i.ID from inserted i;
    select @Name=i.Name from inserted i;
    select @City=i.City from inserted i;
    select @Income=i.Income from inserted i;
    set @Action=' After Insert Trigger.';

    insert into tbl_Audit
           (ID,Name,City,Income,Action,Time)
    values(@ID,@Name,@City,@Income,@Action,getdate());

    PRINT 'AFTER INSERT trigger fired.'

END

When you insert record in tbl_employee it Automatically record on tbl_audit table with update status.

Now, Create Trigger Afetr Delete

When You can delete record on tbl_employee it can automatically store deleted record on tbl_Audit

Now create After Update Trigger as following

ALTER TRIGGER [dbo].[after_updated]
on [dbo].[tbl_employee]
FOR UPDATE
AS
    declare @ID int;
    declare @Name nvarchar(100);
    declare @City nvarchar(100);
    declare @Income decimal(10,2);
    declare @Action varchar(100);

    select @ID=i.ID from inserted i;
    select @Name=i.Name from inserted i;
    select @City=i.City from inserted i;
    select @Income=i.Income from inserted i;


    if update(Name)
        set @Action='Updated Record -- After Update Trigger.';
        if update(City)
        set @Action='Updated Record -- After Update Trigger.';
    if update(Income)
        set @Action='Updated Record -- After Update Trigger.';

    insert into tbl_Audit
           (ID,Name,City,Income,Action,Time)
    values(@ID,@Name,@City,@Income,@Action,getdate());

    PRINT 'AFTER UPDATE Trigger fired.'

When Above three trigger are fire then record are automatically record on tbl_Audit Automatically