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.

  1. AFTER/FOR Trigger
  2. 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