Triggers are kind of stored Procedures which are executed when an event occurs in database .Let us look into triggers with respect to T-SQL or SQL SERVER
KIND OF TRIGGERS:
1.DML (Data manipulation Language) Triggers
2.DDL (Data Definition Language) Triggers
3.Logon Triggers
4.CLR (Common Language RunTime) Triggers
DML Triggers:
These kind of triggers will be executed when an INSERT,UPDATE,DELETE action takes place on a database table or view.
Generally when above mentioned action takes place , we will be using DML trigger to maintain AUDIT Data . For example, if any record is INSERTED/UPDATED/DELETED from a table we will be able to track exactly what happened to that particular record.
As soon as INSERT/UPDATE/DELETE statement starts executing in database or server our trigger will start executing. In the scope of the trigger there are 2 temporary tables are available, they are INSERTED and DELETED which stores the data on which main INSERT/UPDATE/DELETE statement is happening.
There are 2 types of DML triggers.
- AFTER/FOR Trigger
- Instead OF Trigger
AFTER/FOR Trigger:
Using this trigger we will be able to track exactly what is happening on a particular record.
SYNTAX:
CREATE TRIGGER trigger_name
ON {table|view}
[WITH ENCRYPTION|EXECUTE AS]
{FOR|AFTER}
{[INSERT|UPDATE|DELETE ]}
[NOT FOR REPLICATION]
AS
{SQL STATEMENTS }
INSTEAD OF Trigger:
In this type of trigger instead of actual statement i.e.INSERT/UPDATE/DELETE statement the statement in the trigger will be executed
Using Instead of trigger helps us to maintain validation on table columns.
SYNTAX:
CREATE TRIGGER trigger_name
ON {table|view}
[WITH ENCRYPTION|EXECUTE AS]
{INSTEAD OF} {[
INSERT|UPDATE|DELETE ]}
[NOT FOR REPLICATION]
AS
SQL STATEMENTS
DDL Triggers:
These kind of Triggers will be executed in response of CREATE,DROP,ALTER statements occurs in database or server.
There is a major difference between DML and DDL triggers i.e., DML triggers starts executing as soon as main statements starts executing but DDL trigger will be executed in the response of CREATE,DROP,ALTER statements which means completely after executing main statement.
For example if we don't want any user to delete any table then we can write roll back statement in the trigger and we can even print a message on console stating this action is not allowed
In DDL triggers there is only AFTER/FOR trigger . There is no Instead of trigger
SYNTAX:
CREATE TRIGGER trigger_name
ON {table}
[WITH ENCRYPTION|EXECUTE AS]
{FOR|AFTER}
{[CREATE|ALTER|DROP ]}
[NOT FOR REPLICATION]
AS
SQL STATEMENTS
LOGON Trigger:
These triggers comes into action as soon as LOGON EVENT on server is raised i.e., after successful user authentication and before user session starts. If the authentication fails this trigger won't be fired.
Simple example for what we can do with LOGON trigger is we can maintain logs of what that particular user did after logging in to the server. For LOGON trigger also there is only AFTER|FOR trigger.
SYNTAX:
CREATE TRIGGER trigger_name
ON ALL SERVER
[WITH ENCRYPTION]
{FOR|AFTER} LOGON
AS
SQL STATEMENTS
CLR Trigger:
This is a special kind of trigger which is based on .net framework. This stores a DML or DDL statement as an object and triggers when it is called. CLR triggers are introduced in SQL SERVER 2008. These can be coded in .net framework languages like C#, F# and visual basic