A trigger executes automatically when a user attempts a specified data modification statement on a specified table. Syntax create trigger [owner. It must conform to the rules for identifiers and be unique in the database.
The default value for owner is the current user. If you use an owner name to qualify a trigger, you must explicitly qualify the table name the same way.
You cannot use a variable for a trigger name. Trigger conditions determine whether the attempted insert, update, or delete causes the trigger actions to be carried out. The SQL statements often include a subquery preceded by the keyword if. In Example 2, below, the subquery that follows the keyword if is the trigger condition.
Trigger actions take effect when the user action insert, update, or delete is attempted. If multiple trigger actions are specified, they are grouped with begin and end. This allows specified trigger actions to be associated with updates to specified columns see Example 3. More than one column can be specified, and you can use more than one if update statement in a create trigger statement see Example 5. Examples Example 1 Prints a message when anyone tries to add data or change data in the titles table: If the book was written by more than one author, other references to it in titleauthor are also deleted: Prevents updates to the price or advance of a title unless the total revenue amount for that title surpasses its advance amount: Employee2 table includes employees --from both East and West.
The scope of set is limited to just your create trigger command, and resets to your previous setting once the procedure exits. A trigger fires only once per data modification statement. A complex query containing a while loop may repeat an update or insert many times, and the trigger is fired each time. Triggers and referential integrity Triggers are commonly used to enforce referential integrity integrity rules about relationships between the primary and foreign keys of tables or views , to supply cascading deletes, and to supply cascading updates see Examples 2, 3, and 4, respectively.
A trigger fires only after the data modification statement has completed and Adaptive Server has checked for any datatype, rule, or integrity constraint violations. The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If a severe error is detected, the entire transaction is rolled back. You can also enforce referential integrity using constraints defined with the create table statement as an alternative to using create trigger.
See create table and alter table for information about integrity constraints. The deleted and inserted logical tables deleted and inserted are logical conceptual tables. They are structurally identical to the table for which the trigger is defined—that is, the table on which the user action is attempted—and hold the old values or new values of the rows that would be changed by the user action.
Both inserted and deleted tables appear as views on the transaction log, but they are fake tables on syslogs. An update is a delete followed by an insert: Trigger restrictions You can create a trigger only in the current database.
A trigger can reference objects outside the current database. A trigger cannot apply to more than one table. However, the same trigger action can be defined for more than one user action for example, insert and update in the same create trigger statement.
A table can have a maximum of three triggers—one each for insert, update, and delete. Each new trigger in a table or column for the same operation insert, update, or delete overwrites the previous one. No warning message displays before the overwrite occurs.
You cannot create a trigger on a session-specific temporary table. You cannot create a trigger on a view. You cannot create a trigger on a system table. You cannot use triggers that select from a text,uni text, or image column of the inserted or deleted table. Sybase recommends that triggers not include select statements that return results to the user, since special handling for these returned results must be written into every application program that allows modifications to the trigger table.
Triggers and performance In performance terms, trigger overhead is usually very low. The time involved in running a trigger is spent mostly in referencing other tables, which are either in memory or on the database device.
The deleted and inserted tables often referenced by triggers are always in memory rather than on the database device, because they are logical tables.
The location of other tables referenced by the trigger determines the amount of time the operation takes. Setting options within triggers You can use the set command inside a trigger. The set option you invoke remains in effect during the execution of the trigger, then reverts to its former setting. Dropping a trigger You must drop and re-create the trigger if you rename any of the objects referenced by the trigger. When you drop a table, any triggers associated with it are also dropped.
Actions that do not cause triggers to fire A truncate table command is not caught by a delete trigger. Although a truncate table statement is, in effect, like a delete without a where clause it removes all rows , changes to the data rows are not logged, and so cannot fire a trigger. Since permission for the truncate table command defaults to the table owner and is not transferable, only the table owner need worry about inadvertently circumventing a delete trigger with a truncate table statement.
The writetext command, whether logged or unlogged, does not cause a trigger to fire. Triggers and transactions When a trigger is defined, the action it specifies on the table to which it applies is always implicitly part of a transaction, along with the trigger itself.
Triggers are often used to roll back an entire transaction if an error is detected, or they can be used roll back the effects of a specific data modification: When the trigger contains the rollback transaction command, the rollback aborts the entire batch, and any subsequent statements in the batch are not executed. When the trigger contains the rollback trigger , the rollback affects only the data modification that caused the trigger to fire.
The rollback trigger command can include a raiserror statement. Subsequent statements in the batch are executed.
Since triggers execute as part of a transaction, the following statements and system procedures are not allowed in a trigger: