SQL Server triggers are special kind of Stored Procedures that invokes whenever a special event in the database occurs. SQL Server uses two virtual tables INSERTED and DELETED, whenever a trigger is called . These table is used to capture the data of the modified row before and after the event occurs tables , these tables are called as magic tables or virtual tables. Share this: Share

Skype

Email



WhatsApp

Twitter



LinkedIn

Reddit



Facebook

Like this: Like Loading...

SQL Server TRIGGERS are special kind of Stored Procedures that invokes whenever a special event in the database occurs.



SQL Server uses two virtual tables INSERTED and DELETED, whenever a trigger is called . These table is used to capture the data of the modified row before and after the event occurs tables , these tables are called as magic tables or virtual tables.

SQL Server provides three type of triggers:

Data manipulation language (DML) triggers which are invoked automatically in response to INSERT UPDATE DELETE events against tables. There are two types of DML Triggers

After Trigger (using FOR/AFTER CLAUSE) : This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it. Instead of Trigger (using INSTEAD OF CLAUSE) : This type of trigger fires before SQL Server starts the execution of the action that fired it.

Data definition language (DDL) triggers : It fire in response to CREATE ALTER , and DROP

, Logon triggers :It fires in response to LOGON events of SQL Server is raised

Syntax

create trigger [triggername] [ before | after ] { insert | update | delete } on [tblname] [ for each row ] {-- sql statement } create trigger [triggername]: Creates a trigger.

[before | after]:Specifies when the trigger will be executed.

{insert | update | delete}: Specifies the DML operation.

on [tablename]: Specifies the name of the table associated with the trigger.

[for each row]: Specifies a row-level trigger, i.e., the trigger will be executed for each row being affected.

{– sql statement}: SQL logic to be performed as trigger is fired. Read posts on..

DML Triggers

DDL Triggers

LOGON Trigger

Share this: Share

Skype

Email



WhatsApp

Twitter



LinkedIn

Reddit



Facebook



Like this: Like Loading...