Triggers in SQL

Abhisek
Posted by in Sql Server category on for Beginner level | Views : 6525 red flag

This article describes you how to use triggers in SQL.
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.
Page copy protected against web site content infringement by Copyscape

About the Author

Abhisek
Full Name: Abhisek Panda
Member Level: Bronze
Member Status: Member
Member Since: 10/11/2009 6:25:59 AM
Country: India
Abhisek Panda

Thanks and Regards Abhisek Panda Go Green And Save Your Future

Login to vote for this post.

Comments or Responses

Posted by: Raja on: 2/13/2011 | Points: 25
Short and sweet article, for in depth knowledge on triggers people can visit http://msdn.microsoft.com/en-us/library/ms189799.aspx.

Thanks!

Login to post response

Comment using Facebook(Author doesn't get notification)