Creating Triggers

A trigger is a database object. You create a trigger by specifying the current table and the data modification statements that activate the trigger. Then you specify the action or actions the trigger is to take.

A table can have a maximum of three triggers: one update trigger, one insert trigger, and one delete trigger. However, each trigger can perform numerous functions and call up to 16 stored procedures. Each trigger can apply to only one table. However, a single trigger can apply to all three user actions (UPDATE, INSERT, and DELETE).

You cannot create a trigger on a view or on a temporary table, although triggers can reference views or temporary tables.

Although a TRUNCATE TABLE statement is, in effect, like a DELETE statement without a WHERE clause (it removes all rows), it cannot activate a trigger because the TRUNCATE TABLE statement is not logged.

You can use the SET statement inside a trigger. Any SET option you invoke remains in effect during the execution of the trigger and then reverts to its former setting.

    To create a trigger

• From the Manage menu in SQL Enterprise Manager, choose Triggers, select a table from the Tables drop-down list, and then choose New from the Triggers drop-down list.

  (For details, search for "trigger" in SQL Enterprise Manager Help.)

Or

• Use the CREATE TRIGGER statement.

  For example:

  CREATE TRIGGER reminder
  ON titles
  FOR INSERT, UPDATE
  AS RAISERROR (50009,16,10)

  prints a message to the client when anyone tries to add or change data in the titles table.

  (For details, see the CREATE TRIGGER statement in the Microsoft SQL Server Transact-SQL Reference.)