Trigger in SQL Server

Trigger is precompiled set of statement which is executed on some sort of action like Insert data, update data or delete data. Trigger is executed automatically. You don’t worry to execute it manually.

You have to specify the database table name and action (insert, update and delete) while creating the trigger. You can create two types of trigger: after and instead of.

If you specify insert trigger after then that trigger will execute after inserting the data into database table. Instead of trigger will execute instead of executing insert statement.

Trigger is mainly used to do some post-action operation. For example, you want to log every action in database then you can create trigger which will log the information into log table while inserting, updating or deleting the records.

Let’s take an example, whenever any new record is inserted/updated/deleted to EmployeeMaster table then you want to log the information into LogMaster table. You can use below trigger:

 

CREATE TRIGGER trg_EMPLOYEEMASTER_INSERT

on EMPLOYEEMASTER

After Insert

As

begin

Declare @name varchar(1000)

Select @name = EmpName from inserted

Insert into LOGMaster (LogField1) values (@name)

end

This trigger will insert new record into LOGMaster table whenever any user will create new employee. I used one magic table “Inserted”. Inserted table is used to get the newly inserted record into database table. Here Inserted table schema is same as EmployeeMaster database table because we have created trigger on EmployeeMaster table.

Deleted magic table is used to get the record which is deleted from database table which invoke this trigger. This table is used into Delete and Update scenario.  In update scenario, you can get new record information from Inserted table and old record information from Deleted table.

Instead of Trigger:

Instead of trigger will be used to verify the data and do some pre-operation action and insert data from the trigger. Insert/Update/Delete option is not performed if you have created “instead of” trigger.

Before trigger is not available in SQL Server.