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




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 4,161 total views,  2 views today

Leave a Reply

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