Triggers in SQL Server
In this article, I would like to describe one of the most important topic in database – Trigger. My discussions here in this document will be restricted within Microsoft SQL Server Trigger. Throughout this article the following points will be conversed:
1. What is a trigger?
2. When to use triggers
3. Types of triggers with examples
1. What is a trigger?
Triggers are a special kind of database object attached to a specific table. It is a special type of “stored procedure” that executes a batch of SQL code when either an insert, update and/or delete statement is fired on a particular table. The main difference between a stored procedure and trigger is – former is a compiled collection of SQL statements which resides in database though they are not automatically run and they need to be called explicitly by the user but triggers are event driven. When the particular event on that table is called, triggers are automatically executed.
2. When to use triggers ?
Assume a scenario. You just wish to perform some job before or after an insert and/or update and/or delete statement fired on a particular table regularly. In this case, the most elegant solution should be writing a trigger on that table. The full process will occur automatically without any manual hazard. There is a special type of trigger named Instead Of trigger which is performed when user wants to perform a task Instead Of Insert/Update/Delete.
3. Types of triggers with examples
There are three types of triggers in SQL Server:
a) DDL Trigger : They are mainly used for administrative work. It can fire in response to a Transact-SQL event occurred in current database or current server. But keep one thing in mind, DDL triggers can be fired only “after” DDL statements. You cannot use them as INSTEAD OF.
Syntax:
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH [,...n ] ]
{ FOR | AFTER } { event_type |event_group } [,...n ]
AS { sql_statement [ ; ] [,...n] }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause
Example:
Using DDL trigger, you can prevent to create, update or delete any table or database or stored procedure which is very much useful for a complete database.
Create a table named TEST. Following trigger will prevent dropping a table from a database:
Step 1:
CREATE TRIGGER PreventDropTable
ON DATABASE
FOR DROP_TABLE
AS
PRINT 'Dropping Table is not allowed.'
Step 2:
To test the Trigger, write the following command:
DROP TABLE TEST
This should throw following message:
Dropping Table is not allowed.
Step 3:
You can disable the trigger and then drop the table. Last line of the code is used to enable the trigger again.
DISABLE TRIGGER PreventDropTable ON DATABASE;
DROP TABLE TEST;
ENABLE TRIGGER PreventDropTable ON DATABASE;
b) LOGON Trigger : It fires SQL statements or stored procedure in response to LOGON events. Most important thing is - Logon triggers fire after the authentication phase, but before the user session is actually established. If authentication fails, this trigger will not fire. Here all messages that are written in trigger are directly diverted to the SQL Server error log.
Syntax:
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH [,...n] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [,...n ] [ ; ] }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
Example:
Create LOGIN test WITH Password = 'password'
GO
Create Trigger PreventLogon
ON ALL SERVER
AFTER LOGON
AS
IF ORIGINAL_LOGIN = 'test'
ROLLBACK()
Here, trigger PreventLogon will prevent user test to log in the database on all server.
c) DML Trigger: In SQL Server there are mainly two types of DML triggers: AFTER and INSTEAD OF. It is true that there are no BEFORE trigger in SQL though it’s valid for Oracle, the following list of commands cannot be used within a trigger body:
CREATE DATABASE
ALTER DATABASE
DROP DATABASE
LOAD DATABASE
LOAD LOG
RECONFIGURE
RESTORE DATABASE
RESTORE LOG
Syntax:
CREATE TRIGGER [schema_name. ] trigger_name
ON {table | view }
[ WITH [,...n ] ]
{ AFTER | INSTEAD OF }
{ [ INSERT ] [, ] [ UPDATE ] [, ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [,...n ] }
::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]
AFTER trigger is the default one. You use an AFTER trigger when you want a trigger to fire only after SQL Server completes all actions successfully.
Example:
CREATE TRIGGER AfterInsert
ON TEST
AFTER INSERT
AS BEGIN
PRINT 'Inserted Successfully.'
END
This AfterInsert trigger will be fired every time after an row is inserted in TEST Table.
INSTEAD OF triggers take place instead of any SQL action being made.
It can be used as BEFORE triggers available in other RDBMS systems. Let, you define an INSTEAD OF trigger on a table FOR INSERT, the trigger will fire BEFORE the data is inserted into the table. But you can write an INSERT statement within the trigger then the insert will take place. That means, before statement trigger is fired and then event is performed. Now if you don’t write the INSERT statement within trigger then instead of insert, trigger is fired.
Example:
CREATE TRIGGER InsteadOfInsert
ON TEST
INSTEAD OF INSERT
AS BEGIN
PRINT 'Value cannot be inserted. This is a master data table.'
END
This InsteadOfInsert trigger will not allow inserting any row on test table. If you wish to insert value then you have to disable the trigger first and then perform the INSERT operation.
Like it on Facebook, Tweet it or share this article on other bookmarking websites.