.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Triggers in SQL

Posted By:Abhisek Panda       Posted Date: November 29, 2009    Points: 25    Category: DataBase    URL: http://www.dotnetspark.com  
 

Trigger


        A trigger is a special kind of stored procedure that executes automatically when a user attempts the specified data modification statement on the specified table. Miccrosoft SQl server allows the creation of triggers for any given INSERT, UPDATE or DELETE statement.

Trigger for INSERT

Syntax

CREATE TRIGGER trigger_name ON table_name FOR INSERT AS SQLStatement.

trigger_name:- It is the name of the trigger. A trigger name must confirm to the rules for identifiers and must be unique within the database.

table_name:- It is the name of the table on which the trigger is executed and is sometimes called trigger table.

SQLStatement:- The trigger action specified in the Transact-SQL statements go into effect when INSERT, DELETE or UPDATE operation is attempted.

Example1:-
CREATE TRIGGER ti1 ON Student FOR INSERT AS Print 'Record inserted successfully' // This will add trigger for insert operation in table student.

Firing a trigger
INSERT Student VALUES("Abhisek", 108)
The output will be,

Record inserted successfully
1 row(s) affected

Example2:-

CREATE TRIGGER ti2 ON Student FOR INSERT AS select*from inserted

        Inserted is a logical table and structure is similar to the table on which the trigger is defined. That is the table on which the user action is attempted, and new values of the rows that may be changed by the user action.

INSERT Student VALUES("Abinash", 107)

Name            Roll
Abinash         107
1 row(s) affected

Trigger for DELETE

Syntax

CREATE TRIGGER trigger_name ON table_name FOR DELETE AS SQLStatement.
Example1
CREATE TRIGGER td1 ON Student FOR DELETE AS 'Record deleted successfully' 
 This will add a trigger on table student for delete command
DELETE FROM Student WHERE Roll=107
Record inserted successfully
1 row(s) affected

Example2:-

CREATE TRIGGER td2 ON Student FOR DELETE AS select*from deleted

           Deleted
is a logical table and the structure is similar to the table on which the trigger is defined. That is, the table on which the user action is attempted, and the old values of the rows that may be changed by the user action.

DELETE FROM Student WHERE Roll=107

Name              Roll
Abinash           107
1 row(s) affected

Trigger for Update

Syntax:-

CREATE TRIGGER trigger_name ON table_name FOR UPDATE AS SQLStatement.
Example1:-
CREATE TRIGGER tu1 ON Student FOR UPDATE AS 'Record updated successfully' 

This will add a trigger on table student for delete command

UPDATE Student set Name='Abhisek Panda' whwre roll=108
Record updated successfully
1 row(s) affected

Example2:-

CREATE TRIGGER tu2 ON Student FOR UPDATE AS select*from deleted select*from inserted

         Deleted and inserted are logical tables and are structurally similar to the table on which the trigger is defined. That is, the table on which the user action is attempted, and hold the old values and new values of the rows that may be changed by the user action.

UPDATE Student set Name='Abhisek Panda' whwre roll=108
Name              Roll
Abhisek           108

Name                    Roll
Abhisek Panda     108
1 row(s) affected


NB:- First you have to create and execute that trigger and then you have to fire it.

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend