A TRIGGER is a special type of stored procedure, which is ‘fired’ automatically when the data in a specified table is modified. It is invoked when an INSERT, UPDATE, or DELETE action is performed on a table.
Creating a Trigger
Syntax:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{
{ FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[
{
IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) … ]
|
&nbs
trigger_name
Is the name of the trigger. It should conform to the rules for identifiers.
table
Is the table on which the trigger is to be created.
WITH ENCRYPTION
If this option is specified, the syscomments entries that contain the text of CREATE TRIGGER will be encrypted.
DELETE, INSERT, UPDATE
These keywords specify on which action the trigger should be activated. One of these keywords or any combination thereof in any order can be used.
WITH APPEND
This specifies that an additional trigger should be added.
IF UPDATE
This checks for an INSERT or UPDATE to a specified column and is not used with the DELETE operations. One or more columns can be specified here.
column
It is the name of the column to check for an INSERT or UPDATE action.
IF
Checks, whether the mentioned column or columns were inserted or updated.
COLUMNS_UPDATED
Can be used anywhere inside the body of the trigger.
bitwise_operator
It is the bitwise-operator used for the comparison.
updated_bitmask
This is the integer bitmask of the columns that are actually updated or inserted.
comparison_operator
Is the comparison operator. The equals (=) sign checks whether all columns specified in the updated_bitmask are actually updated. The greater (>) than symbol checks whether any or some of the columns specified in updated_bitmask are updated.
column_bitmask
Is the integer bitmask of those columns to check whether they are updated or inserted.
sql_statement
Is the trigger condition(s) and action(s).
A TRIGGER is created using the CREATE TRIGGER command.
Examples
Code:
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname =(SELECT Name FROM INSERTED)
PRINT ‘THE STUDENT ‘ + @Newname + ‘ IS ADDED.’;
Explanation:
Executing this creates a new trigger named trigAddStudents, which is attached to the ‘Students’ table. Whenever a new record is added to the ‘Students’ table, SQL Server will automatically execute our trigger.
Let’s look at the above example in detail:
CREATE TRIGGER trigAddStudents
ON Students
–A new trigger object, trigAddStudents, should be attached to the ‘Students’ table.
FOR INSERT
–The trigger will be fired when an INSERT command is executed on the ‘Students’ table
(If we would like to handle the INSERT and UPDATE events, we would have to use FOR INSERT, UPDATE).
AS
DECLARE @Newname VARCHAR(100)
SELECT @Newname = (SELECT Name FROM Inserted)
–When the trigger is called the queries after the AS keyword is executed.
SELECT Name FROM INSERTED
–The SQL command retrieves the ‘Name’ field from the inserted table. The inserted table, which contains all the values we inserted using the INSERT command.
(Similarly for UPDATE and DELETE command we can use UPDATED and DELETED.)
PRINT ‘THE STUDENT ‘ + @Newname + ‘ IS ADDED.’
–Prints the name selected from the INSERTED table
Language(s): MS SQL Server
Code:
INSERT INTO Students VALUES (6,’George Mathew’, 1, 25542, 1);
Output:
THE STUDENT George Mathew IS ADDED.
(1 row(s) affected)
Explanation:
When the above INSERT statement is executed, the new record is added to the ‘Students’ table and automatically calls the trigger.
Note:
1. The name of a trigger should follow the rules for identifiers.
2. The CREATE TRIGGER must be the first statement in the batch.
3. Triggers cannot be created on a view, temporary table or system table, but they can reference views or temporary tables.
4. The system tables should not be referenced in a trigger. Use the Information Schema Views instead.
Modifying a Trigger
Syntax:
ALTER TRIGGER trigger_name
ON table
[ WITH ENCRYPTION ]
{
{
FOR { [ DELETE ] [ , ] [ UPDATE ] [ , ] [ INSERT ] }
[ NOT FOR REPLICATION ]
AS
sql_statement [ …n ]
}
|
{
FOR { [ INSERT ] [ , ] [ UPDATE ] }
[ NOT FOR REPLICATION ]
AS
{
IF UPDATE ( column )
&nb
A trigger can be modified by either, deleting the trigger and recreating a new trigger or by altering the existing trigger. The parameters from the ALTER TRIGGER statement are similar to those from the CREATE TRIGGER statement.
Examples
Code:
ALTER TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
PRINT ‘THE TRIGGER IS CHANGED.’;
Output:
The command(s) completed successfully.
Explanation:
The above example modifies the trigger created earlier by changing the print that is to be displayed when the trigger is ‘fired’.
Language(s): MS SQL Server
Code:
INSERT INTO Students VALUES (7,’Tom George’, 1, 45452, 2);
Output:
THE TRIGGER IS CHANGED.
(1 row(s) affected)
Explanation:
When the INSERT statement is executed, the modified trigger is ‘fired.’
Renaming a Trigger
Syntax:
sp_rename ‘obj_name’, ‘obj_newname’
obj_name
The current name of the trigger.
obj_newname
The new name of the trigger.
A trigger can also be renamed. The new name should follow the same rules that are used while naming a trigger.
Examples
Code:
sp_rename ‘trigAddStudents’, ‘trigAddStudentsNew’;
Output:
Caution: Changing any part of an object name could break scripts and stored procedures. The object was renamed to ‘trigAddStudentsNew’.
Explanation:
Here the name of trigAddStudents is changed to trigAddStudentsNew.
Viewing a Trigger
Syntax:
1. To view the types of triggers on a table:
sp_helptrigger table1 [ , type ]
2. To view a trigger:
sp_helptext trigger1
table1
Is the name of the table for which to return trigger information.
type
Is an optional parameter where the type of the trigger is to be specified. Its value can be INSERT, UPDATE or DELETE.
trigger1
Is the name of the trigger for which the definition information is to be displayed.
SQL server allows us to determine the types of triggers on a table, view information about a trigger, to view a trigger, and to view the dependencies of it.
Examples
Code:
sp_helptrigger Students;
Output:
trigger_name trigger_owner isupdate isdelete isinsert
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
trigAddStudentsNew dbo 0 0 1
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(1 row(s) affected)
Explanation:
The above example returns all the information about the triggers on the ‘Students’ table – the name of the triggers, type of the triggers etc.
Language(s): MS SQL Server
Code:
sp_helptrigger Students, ‘UPDATE’;
Output:
trigger_name trigger_owner isupdate isdelete isinsert
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
________________________________________ ________________________________________ ________________________________________ ________________________________________ ________________________________________
(0 row(s) affected)
Explanation:
If we specify the type of the trigger, we only get the information about that type of trigger.
Language(s): MS SQL Server
Code:
sp_helptext trigAddStudentsNew;
Output:
CREATE TRIGGER trigAddStudents
ON Students
FOR INSERT
AS
PRINT ‘THE TRIGGER IS CHANGED.’
Explanation:
Here the trigger text is displayed.
Deleting a Trigger
Syntax:
DROP TRIGGER trigger1, …
trigger1
Is the name of the trigger(s) to be removed.
If a trigger is deleted, the table on which the trigger was created and the data in the table is not in any way affected. However, if the table is deleted, any trigger associated with the table is also deleted.
Examples
Code:
DROP TRIGGER trigAddStudentsNew;
Output:
The command(s) completed successfully.
Explanation:
Here the trigger, trigAddStudentsNew, is deleted from the database.