Home » SQL Triggers

SQL Triggers

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, and DELETE  events against tables. There are two types of DML Triggers
    1.  After Trigger (using FOR/AFTER CLAUSE) : This type of trigger fires after SQL Server finishes the execution of the action successfully that fired it.
    2. 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 statements.
  • 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.

 

DML Triggers

DDL Triggers

LOGON Trigger




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.