Article
    C#
    ADO.Net
    .NET
    ASP.Net & Web Forms
    Custom Controls
    Web Development
    Exception Handling
    XML
    Database
    Security in .Net
    Testing
    Web Services
    Windows Services
    Windows Controls
    WCF
    AJAX
    WPF
    XAML
    Reporting
    Setup
    VB.Net
    LINQ
    JQuery
    SilverLight
    JavaScript
    HTML5
    Crystal Report
    Cloud Computing
    Share Point
    Visual C++
    MVC
    Android
    PHP
    Java
    HTML
    WordPress
    Joomla
    Products
    Drupal
    Windows Phone
    JSON
    LightSwitch
    iPhone/iPad
    Ruby on Rails
    IIS 7
    Windows 8
    CSS/CSS3
    Excel
    MS Access
    Shortcut Keys
    Visual SourceSafe
    Team Foundation Server
    API(s)
    Sencha-Touch
    Single Page App
    Bootstrap
Follow Us
Follow _MindStick_ on Twitter View MindStick Software's LinkedIn profile View MindStick Software's Facebook profile
Top Contributor
Advertisement
Advertise with Us
Mindstick
Article Article  Forum Forum  Blog Blog  Quiz Quiz  Beginner Beginner  Careers Careers  Contact Contact  Login Login  
Home | Product | Services | About Us | Interview | DeveloperSection | Submit an Article | Submit Blog

Home >> Database >> Trigger in SQL server
Trigger in SQL server
Trigger in SQL server


by Arun Singh on 7/18/2011 8:29:53 PM

Views: 7358       Comments: 0

Trigger in SQL Server

Triggers:

Trigger is special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table (like insert, update, delete query etc.). A Trigger is a block of code, fired whenever data in the underlying table is affected by any of the Data Manipulation Language (DML) statements in SQL server.

The difference between a Trigger and a Stored Procedure is that the trigger is former is attached to a table and is only fired when an INSERT, UPDATE or DELETE occurs. You specify the modification action’s that fire the trigger when it is created.

There are many types of trigger available in SQL server in which some important trigger are given as follows:

·         DML Trigger

·         DDL Trigger

·         Logon Trigger

DML Trigger:

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are ‘INSERT’,’ UPDATE’, or 'DELETE’ statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

AFTER Trigger for INSERT event:

When trigger fired on insert event on any table then it is called Trigger for ‘Insert’.

Syntax:

----- SYNTAX DEMONSTRATION OF TRIGGER ON INSERT EVENT ON ANY TABLE----

CREATE TRIGGER <TRIGGER_NAME> OR <VIEW_NAME>

ON <TABLE_NAME>

<AFTER | FOR >INSERT

AS

---TRIGGER BODY OR EXECUTION CODE WHICH WANT TO EXECUTE-----

GO

Example: Creating Trigger on AFTER Insert Event

------DEMONSTRATION OF CREATING TRIGGER ON INSERT EVENT

CREATE TRIGGER INSERT_TRIGGER

ON USERLOGININFO

AFTER INSERT

AS

PRINT 'INSERT TRIGGER EXECUTED AFTER INSERT VALUE INTO TABLE'

GO

AFTER  Trigger for Update Event:

When trigger fired on insert event on any table then it is called Trigger for ‘Update’.

Syntax: Creating Trigger for AFTER Update event

----- SYNTAX DEMONSTRATION OF TRIGGER ON UPDATE EVENT ON ANY TABLE----

CREATE TRIGGER <TRIGGER_NAME> OR <VIEW_NAME>

ON <TABLE_NAME>

<AFTER | FOR>  UPDATE

AS

---TRIGGER BODY OR EXECUTION CODE WHICH WANT TO EXECUTE-----

GO

Example: Creating Trigger on AFTER Update Event

---- DEMONSTRATION FOR CREATING TRIGGER FOR UPDATE EVENT ON TABLE

CREATE TRIGGER UPDATE_TRIGGER

ON USERLOGININFO

AFTER UPDATE

AS

PRINTUPDATE TRIGGER EXECUTED AFTER INSERT VALUE INTO TABLE'

GO

AFTER  Trigger for Delete Event:

When trigger fired on insert event on any table then it is called Trigger for ‘Update’.

Syntax: Creating Trigger for AFTER Delete event

----- SYNTAX DEMONSTRATION OF TRIGGER ON DELETE EVENT ON ANY TABLE----

CREATE TRIGGER <TRIGGER_NAME> OR <VIEW_NAME>

ON <TABLE_NAME>

<AFTER | FOR> DELETE

AS

---TRIGGER BODY OR EXECUTION CODE WHICH WANT TO EXECUTE-----

GO

Example: Creating Trigger on AFTER DELETE Event

---- DEMONSTRATION FOR CREATING TRIGGER FOR DELETE EVENT ON TABLE

CREATE TRIGGER DELETE_TRIGGER

ON USERLOGININFO

AFTER DELETE

AS

PRINT DELETE TRIGGER EXECUTED AFTER INSERT VALUE INTO TABLE'

GO

DDL Trigger:

DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

Trigger for Create Table:

Syntax: Creating DDL Trigger for CREATE table into database

---- SYNTAX DEMONSTRATION TO CREATING DDL TRIGGER FOR CREATING TABLE INTO DATABASE

CREATE TRIGGER <TRIGGER_NAME>

ON DATABASE

FOR CREATE_TABLE

AS

---SQL EXECUTABLE CODE FOR CREAT TABLE EVENT---

GO

Example: Creating DDL Trigger for CREATE table into database

----DEMONSTRATION TO CREATING DDL TRIGGER FOR CREATING TABLE INTO DATABASE

CREATE TRIGGER CREATE_TABLEE

ON DATABASE

FOR CREATE_TABLE

AS

PRINT 'TRIGGER FOR CREATE TABLE IN DATABASE'

GO

Trigger for Drop Table:

Syntax: Creating DDL Trigger for DROP table into database

---- SYNTAX DEMONSTRATION TO CREATING DDL TRIGGER FOR DROPPING TABLE INTO DATABASE

CREATE TRIGGER <TRIGGER_NAME>

ON DATABASE

FOR DROP_TABLE

AS

---SQL EXECUTABLE CODE FOR DROP TABLE EVENT---

GO

Example: Creating DDL Trigger for DROP table into database

----DEMONSTRATION TO CREATING DDL TRIGGER FOR DROP TABLE INTO DATABASE

CREATE TRIGGER DROP_TABLEE

ON DATABASE

FOR DROP_TABLE

AS

PRINT 'TRIGGER FOR DROP TABLE IN DATABASE'

GO

Trigger for Alter Table:

Syntax: Creating DDL Trigger for ALTER table into database

---- SYNTAX DEMONSTRATION TO CREATING DDL TRIGGER FOR ALTERING TABLE INTO DATABASE

CREATE TRIGGER <TRIGGER_NAME>

ON DATABASE

FOR ALTER_TABLE

AS

---SQL EXECUTABLE CODE FOR ALTER TABLE EVENT---

GO

Example: Creating DDL Trigger for ALTER table into database

----DEMONSTRATION TO CREATING DDL TRIGGER FOR ALTER TABLE INTO DATABASE

CREATE TRIGGER ALTER_TABLEE

ON DATABASE

FOR ALTER_TABLE

AS

PRINT 'TRIGGER FOR ALTER TABLE IN DATABASE'

GO

Logon Trigger:

Logon Trigger event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.

Syntax: Creating LOGON Trigger into database

-----SYNTAX DEMONSTRATION OF CREATING LOGON TRIGGER

CREATE TRIGGER <TRIGGER_NAME>

ON ALL SERVER FOR LOGON

AS

--- SQL QUERY TO RETRIEVE INFORMATION OR STORED INFORMATION

GO

Example: Creating LOGON Trigger into database

---- CREATE NEW DATABASE TO STORE TRIGGER

CREATE DATABASE LOGON_TRIGGER

 

---- CREATE TABLE USING ABOVE DATABASE

USE LOGON_TRIGGER

----TABLE WHICH STORED LOGIN INFORMATION OF LOGIN SQL SERVER USER

CREATE TABLE LOGIN_USER

(

  SYSTEMUSER VARCHAR(50),

  DATABASEUSER VARCHAR(50),

  LOGTIME DATETIME

)

 

-----DEMONSTRATION OF LOGON TRIGGER

 

CREATE TRIGGER LOGON_TRIGGERR

ON ALL SERVER FOR LOGON

AS

INSERT INTO LOGON_TRIGGER.dbo.LOGIN_USER VALUES (SYSTEM_USER,USER,GETDATE())

GO

Instead Of Trigger in SQL Server:

AFTER triggers do not work for views, so we will discuss the properties and functionality of INSTEAD OF triggers. INSTEAD OF triggers cause their source DML operation to skip and they just execute the code provided inside them. Actual insert, delete or update operation do not occur at all. However they have their associated inserted and deleted tables simulating the DML operation. Inserted and deleted tables are widely used in operations inside triggers.

INSTEAD OF triggers give you the ability to evaluate the changes that would have taken place if the data modification statement had actually executed. Like AFTER triggers, each INSTEAD OF trigger gives you access to two virtual tables called Inserted and Deleted. For a DELETE trigger, the virtual table Deleted holds all the deleted rows, and for an INSERT trigger, the virtual table Inserted holds all the new rows. An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

An UPDATE trigger populates both the Inserted and Deleted tables; the Deleted table stores the old version of the rows, and the Inserted table stores the new version.

Basically there are three types of Instead Of Trigger in SQL server which are namely define as follows:

·         Instead Of Insert Trigger

·         Instead Of Update Trigger

·         Instead Of Delete Trigger

Instead Of Insert Trigger:

INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement. Usually, the INSTEAD OF INSERT trigger is defined on a view to insert data into one or more base tables.

Syntax: Creating Instead Of Insert Trigger

-----SYNTAX DEMONSTRATION OF INSTEAD OF INSERT TRIGGER--

CREATE TRIGGER <TRIGGER_NAME>

ON <VIEW_NAME> OR <TABLE_NAME>

INSTEAD OF INSERT

AS

BEGIN

----INSERT SQL QUERY TO INSERT DATA INTO TABLE--

--TRIGGER BODY SQL CODE---

END

GO

Example: Creating Instead Of Insert Trigger

----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF INSERT TRIGGER

 CREATE VIEW TEST_INSERT_INSTEADOF

 AS

 SELECT * FROM Userlogin

 ------DEMONSRATION OF CREATING INSTEAD OF INSERT TRIGGER--

  CREATE TRIGGER TEST_INSTEADOF_TRIGGER

  ON TEST_INSERT_INSTEADOF

  INSTEAD OF INSERT

  AS

  INSERT INTO Userlogin

  SELECT * FROM INSERTED

  GO

Instead of Delete Trigger:

INSTEAD OF DELETE triggers can be defined on a view or table to replace the standard action of the DELETE statement. Usually, the INSTEAD OF DELETE trigger is defined on a view to modify data in one or more base tables. DELETE statements do not specify modifications to existing data values. DELETE statements specify only the rows that are to be deleted.

Syntax: Creating Instead of Delete Trigger

  -----SYNTAX DEMONSTRATION OF INSTEAD OF DELETE TRIGGER--

CREATE TRIGGER <TRIGGER_NAME>

ON <VIEW_NAME> OR <TABLE_NAME>

INSTEAD OF DELETE

AS

BEGIN

----DELETE SQL QUERY TO INSERT DATA INTO TABLE--

--TRIGGER BODY SQL CODE---

END

GO

Example: Creating Instead of Delete Trigger

----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF DELETE TRIGGER

 CREATE VIEW TEST_DELETE_INSTEADOF

 AS

 SELECT * FROM Userlogin

 ------DEMONSRATION OF CREATING INSTEAD OF DELETE TRIGGER--

  CREATE TRIGGER TEST_INSTEADOF_TRIGGER

  ON TEST_DELETE_INSTEADOF

  INSTEAD OF DELETE

  AS

  INSERT INTO Userlogin

  SELECT * FROM DELETEED

  GO

Instead of Update Trigger:

INSTEAD OF UPDATE triggers can be defined on a view or table to replace the standard action of the UPDATE statement. Usually, the INSTEAD OF UPDATE trigger is defined on a view to modify data in one or more base tables. UPDATE statements that reference views with INSTEAD OF UPDATE triggers must supply values for all non null view columns referenced in the SET clause.

Syntax: Creating Instead of Update Trigger

  -----SYNTAX DEMONSTRATION OF INSTEAD OF UPDATE TRIGGER--

CREATE TRIGGER <TRIGGER_NAME>

ON <VIEW_NAME> OR <TABLE_NAME>

INSTEAD OF UPDATE

AS

BEGIN

----UPDATE SQL QUERY TO INSERT DATA INTO TABLE--

--TRIGGER BODY SQL CODE---

END

GO

Example: Creating Instead of Update Trigger

----CREATE VIEW FOR DEMONSTRATION OF CREATING INSTEAD OF UPDATE TRIGGER

 CREATE VIEW TEST_UPDATE_INSTEADOF

 AS

 SELECT * FROM Userlogin

 ------DEMONSRATION OF CREATING INSTEAD OF UPDATE TRIGGER--

  CREATE TRIGGER TEST_INSTEADOF_TRIGGER

  ON TEST_UPDATE_INSTEADOF

  INSTEAD OF DELETE

  AS

  UPDATE Userlogin SET Id = deleted.id, Name = deleted.name 

 

  GO

 

Magic Table:

In SQL server magic table is nothing more than an internal table which is created by the SQL server to recover recently inserted, deleted and updated data into SQL server database. Basically there are two types of magic table in SQL server namely: inserted and deleted, update can be performed with help of these twos. Generally we cannot see these two table, we can only see it with the help Trigger’s in SQL server. Let’s see the following example:

INSERTED Magic Table:

When we insert record into table then ‘inserted’ magic table are created and recently inserted record are available in this table we can access this record with the help of Trigger’s. Let’s see the demonstration of inserted magic table.

Example:

--- CREATE TABLE TO DEMONSTRATION OF INSERT MAGIC TABLE ----

CREATE TABLE INSERT_MAGIC

(

ID INT,

TRIGGER_MESSAGE VARCHAR(50)

 

)

 -------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_INSERT

  ON USERLOGIN

  INSTEAD OF INSERT

  AS

  BEGIN

  DECLARE @ID INT

  SELECT @ID = (SELECT ID FROM inserted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,'RECORD ADDED')

  END

  GO

Note:Here ‘inserted’ is insert magic table

DELETED Magic Table:

When we delete record from the table then SQL automatically create a deleted magic table which holds are deleted data from actual table if we want to recover it then we can access that data from deleted magic table. Let’s see demonstration of recover data from deleted magic table.

Example: Creating Trigger for Deleted Magic table

-------DEMONSTRATION OF CREATING TRIGGER TO EXPLORE INSERTED MAGIC TABLE

  CREATE TRIGGER TRI_MAGIC_DELETE

  ON USERLOGIN

  INSTEAD OF INSERT

  AS

  BEGIN

  DECLARE @ID INT

  DECLARE @NAME VARCHAR(50)

  SELECT @ID = (SELECT ID FROM deleted)

  SELECT @NAME = (SELECT NAME FROM deleted)

  INSERT INTO INSERT_MAGIC VALUES (@ID,@NAME)

  END

  GO

 

Report Abuse Form
Reason:    
 

Title :
Comment :
Text ColorBackground Color
BoldItalicUnderline
LeftCenterRightJustify
Ordered ListBulleted List
IndentOutdent
Horizontal Rule
SubscriptSuperscript
HyperlinkImage
Design ModeDesign
View HtmlHtml
     
 
Latest Article by Arun SinghRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 4848
Advertisement
MindStick Cleaner
Advertise with Us
  
Copyright © 2009 - 2014MindStick. All Rights Reserved.