Triggers

Triggers are a special type of stored procedure that automatically execute when a DML or DDL statement associated with the trigger is executed. You can’t execute a trigger directly.
DML triggers are created against a table or a view and are defined for a specific event: INSERT, UPDATE, or DELETE. Regardless of the number of rows that are affected, a trigger fires only once for an action.

When you execute the event for which a trigger is defined, SQL Server automatically executes the code within the trigger.

When a trigger is defined as AFTER, the trigger fires after the modification has passed all constraints. If a modification fails a constraint, such primary key constraint, the trigger is not executed.
Note:
AFTER triggers are defined only for tables, and multiple AFTER triggers can be defined for the same action. If you have multiple triggers created for the same action, you can specify the first and last triggers to fire by using the sp_settriggerorder system stored procedure.

A trigger defined with the INSTEAD OF clause causes the trigger code to be executed as a replacement for INSERT, UPDATE, or DELETE. You can define a single INSTEAD OF trigger for a given action. Although INSTEAD OF triggers can be created against both tables and views.

Let's see this example of a DDL trigger:


Now if you try to delete any table or alter the schema of tables you will get this error:



Note:
You can fire a DDL trigger when you grant, revoke, or deny permissions at either a server or a database level.

While DML triggers have access to the virtual tables called inserted and deleted tables, DDL triggers and logon triggers have access to the EVENTDATA function. For example when inserting a recode into a table, SQL Server creates a virtual table call INSERTED and loads data into the inserted table then executes the trigger statements and writes the related data pages.