Overview

• Triggers create a DML, DDL, or logon trigger in SQL Server.
  • A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.
  • However, it has no parameters, you cannot invoke it and it has INSERTED and DELETED local pseudo-tables.
• 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.
  • These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.
• DDL triggers execute in response to a variety of data definition language (DDL) events.
  • These events primarily correspond to T-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
• Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.

DML Triggers

• DML triggers are frequently used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the foreign keys.
• However, DRI does not provide cross-database referential integrity.
  • Referential integrity refers to the rules about the relationships between the primary and foreign keys of tables.
  • To enforce referential integrity, use the PRIMARY KEY and FOREIGN KEY constraints.
  • If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and before the AFTER trigger execution.
  • If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not fired.
  • INSTEAD OF triggers are the way we update VIEW objects and get around the mathematical limits of RDBMS.
  • An INSTEAD OF trigger performs the trigger body instead the database action that fired it.
  • Therefore, we can instert (or update) the appropriate tables based on the changes to the view.
• The order of AFTER triggers to be executed on a table can be specified by using sp_settriggerorder.
  • Only one first and one last AFTER trigger for each INSERT, UPDATE, and DELETE operation can be specified on a table.
  • If there are other AFTER triggers on the same table, they are randomly executed.
• If an ALTER TRIGGER statement changes a first or last trigger, the first or last attribute set on the modified trigger is dropped, and the order value must be reset by using sp_settriggerorder.

EXEC sp_settriggerorder @triggername = 'Sales.uSalesOrderHeader', @order = 'First', @stmttype = 'UPDATE';

• An AFTER trigger is executed only after the triggering SQL statement has executed successfully.
• This successful execution includes all referential cascade actions and constraint checks associated with the object updated or deleted and will not recursively fire an INSTEAD OF trigger on the same table.
• If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively.
  • Instead, the statement is processed as if the table had no INSTEAD OF trigger and starts the chain of constraint operations and AFTER trigger executions.
  • For example, if a trigger is defined as an INSTEAD OF INSERT trigger for a table, and the trigger executes an INSERT statement on the same table, the INSERT statement executed by the INSTEAD OF trigger does not call the trigger again.
  • The INSERT executed by the trigger starts the process of performing constraint actions and firing any AFTER INSERT triggers defined for the table.
• If an INSTEAD OF trigger defined on a view executes a statement against the view that would ordinarily fire the INSTEAD OF trigger again, it is not called recursively.
  • Instead, the statement is resolved as modifications against the base tables underlying the view.
  • In this case, the view definition must meet all the restrictions for an updatable view.

Nested Triggers

• Triggers can be nested to a maximum of 32 levels. If a trigger changes a table on which there is another trigger, the second trigger is activated and can then call the third trigger, and so on.
• If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger is canceled.
• When a T-SQL trigger executes managed code by referencing a CLR routine, type, or aggregate, this reference counts as one level against the 32-level nesting limit.
• Methods invoked from within managed code do not count against this limit.