CREATE TRIGGER (Transact-SQL)

Creates a DML, DDL, or logon trigger in SQL Server 2012. A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server. 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. For more information, see DML Triggers.DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-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. Triggers can be created directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server. SQL Server allows for creating multiple triggers for any specific statement.

For some examples and considerations of creating SQL triggers, see:

1.BEFORE SQL triggers
2.AFTER SQL triggers
3.Handlers in SQL triggers
4.SQL trigger transition tables

1. BEFORE SQL triggers
BEFORE triggers may not modify tables, but they can be used to verify input column values, and also to modify column values that are inserted or updated in a table. In the following example, the trigger is used to set the fiscal quarter for the corporation before inserting the row into the target table.


CREATE TRIGGER TransactionBeforeTrigger BEFORE INSERT ON TransactionTable
REFERENCING NEW AS new_row
FOR EACH ROW MODE DB2ROW
BEGIN
  DECLARE newmonth SMALLINT;
SET newmonth = MONTH(new_row.DateOfTransaction);
  IF newmonth < 4 THEN
    SET new_row.FiscalQuarter=3;
  ELSEIF newmonth < 7 THEN
    SET new_row.FiscalQuarter=4;
  ELSEIF newmonth < 10 THEN
    SET new_row.FiscalQuarter=1;
  ELSE
    SET new_row.FiscalQuarter=2;
  END IF;
END


2. AFTER SQL triggers
The WHEN condition can be used in an SQL trigger to specify a condition. If the condition evaluates to true, then the SQL statements in the SQL trigger routine body are executed. If the condition evaluates to false, the SQL statements in the SQL trigger routine body are not executed, and control is returned to the database system. In the following example, a query is evaluated to determine if the statements in the trigger routine body should be run when the trigger is activated.


3. Handlers in SQL triggers
A handler in an SQL trigger gives the SQL trigger the ability to recover from an error or log information about an error that has occurred while executing the SQL statements in the trigger routine body.In the following example, there are two handlers defined: one to handle the overflow condition and a second handler to handle SQL exceptions.

4. SQL trigger transition tables
An SQL trigger may need to refer to all of the affected rows for an SQL insert, update, or delete operation. This is true, for example, if the trigger needs to apply aggregate functions, such as MIN or MAX, to a specific column of the affected rows. The OLD_TABLE and NEW_TABLE transition tables can be used for this purpose. In the following example, the trigger applies the aggregate function MAX to all of the affected rows of the table StudentProfiles.