SQL Server 2008: Trigger types

Hello Friends,

Yesterday I was working on creating a trigger on our SQL Server 2008 database, while brainstorming with a colleague about choosing correct type of trigger which is best suited for that particular requirement we came to a question like

"how many types of trigger's does SQL server or TSQL supports ?"

the instant answer was "2" !!
One is "After trigger" and another is "Instead Of trigger" .. I don't know why but I was not convinced by our solution to that particular business requirement. Somehow I managed to steal some more time for digging it more.

After some reading I came to know the fact that the trigger types we were referring to are actually types of DML triggers and there are two more trigger types,

Here is brief description about Trigger Types:

Following are actual Trigger Types:
1. DML Trigger
2. DDL Trigger
3. Logon Trigger

DML Triggers:

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
DML Triggers can be of two types.

1. FOR | AFTER
AFTER specifies that the DML trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger fires.

AFTER is the default when FOR is the only keyword specified.

2. INSTEAD OF
Specifies that the DML trigger is executed instead of the triggering SQL statement, therefore, overriding the actions of the triggering statements. INSTEAD OF cannot be specified for DDL or logon triggers.

DDL Triggers:

DDL Triggers are a special kind of trigger that fire in response to Data Definition Language (DDL) statements. They can be used to perform administrative tasks in the database such as auditing and regulating database operations.

Logon Triggers:

Logon triggers fire stored procedures in response to a LOGON event. This event is raised when a user session is established with an instance of SQL Server. Logon triggers fire after the authentication phase of logging in finishes, but before the user session is actually established.