Trigger

                A trigger is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updating of data. It is a database object which is bound to a table and gets executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action on the table that they are assigned to.

Types of Triggers

a)  Login Trigger

b)  DDl Trigger

c)   DML Trigger

       Logon triggers fires in response to a LOGON event i.e. when a user session is established.

Session is established before authentication. Logon triggers fire after the authentication and phase of logging in finishes, but before the user session is actually established. Therefore, all messages originating inside the trigger that would typically reach the user, such as error messages and messages from the PRINT statement, are diverted to the SQL Server error log. Logon triggers do not fire if authentication fails.

Login Triggers is useful for the following purpose

         Restrict a user from simultaneously opening more than no of connections with the Server.

         Login related auditing in table for tracking purpose

USE master;

GO

CREATE LOGIN Testaccount WITH PASSWORD = MUST_CHANGE ' MUST_CHANGE,

    CHECK_EXPIRATION = ON;

GO

GRANT VIEW SERVER STATE TO login_test;

GO

CREATE TRIGGER Tr_ login
ON ALL SERVER FOR LOGONAS

BEGIN

  Print “welcome”   -- any task 

END;

DDl Trigger

                  Trigger firing on DDL commands (Like  create / drop / alter on  server objects like table /sp / function / schema ….).

For more info Please go throw DDl events available for triggers    http://msdn.microsoft.com/en-us/library/ms189871(v=sql.90).aspx  

DDL Triggers is useful for the following purpose:
• Auditing
• Regulating database or server operation
• Securing the database or server from malpractice
• Issue an alert when a DDL event occurs in Database or Server

Ie.

USE AdventureWorks

GO

CREATE TRIGGER TR_PreventcreatedSP             

ON DATABASE            

FOR CREATE_PROCEDURE            

AS

PRINT 'No one can create store proc till PreventcreatedSP is available'

ROLLBACK;

DML Trigger

Triggers firing on DML commands (Insert /Update/Delete)

DML Triggers is useful for the following purpose:

         DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.

         They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints.

DML triggers can reference columns in other tables (check cannot)..

         They can evaluate the state of a table before and after a data modification and take actions based on that difference (concept of Magic table ).

         Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

         Constraints can communicate about errors only through standardized system error messages. If your application requires, or can benefit from, customized messages and more complex error handling, you must use a trigger.

         DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. However, FOREIGN KEY constraints are usually used for this purpose.

Events

AFTER trigger (AFTER triggers are executed after the action of the Dml action)

INSTEAD OF trigger is used to perform error or value checking on one or more columns and the perform additional actions before insert, updating or deleting the row or rows.

Ie. 

CREATE TRIGGER tr_TableInsertTrig ON Table

INSTEAD OF INSERT

AS ...

CREATE TRIGGER tr_TableInsertTrig ON Table

AFTER INSERT

AS ...

Event chart for DDL