Skip to content
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

  1. After Trigger (using FOR/AFTER CLAUSE)
  2. Instead of Trigger (using INSTEAD OF CLAUSE)

DDL Triggers

LOGON Trigger

 




 

Loading

Leave a Reply

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