Triggers in SQL

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.

If you like this article, subscribe to our RSS Feed. You can also subscribe via email to our Interview Questions, Codes and Forums section.