create trigger

Description

Creates a trigger, a type of stored procedure that is often used for enforcing integrity constraints. A trigger executes automatically when a user attempts a specified data modification statement on a specified table.

Syntax

create trigger [owner .]trigger_name
	on [owner .]table_name
	for {insert , update , delete} 
	as SQL_statements

Or, using the if update clause:

create trigger [owner .]trigger_name
	on [owner .]table_name
	for {insert , update} 
	as 
		[if update (column_name ) 
			[{and | or} update (column_name )]...] 
			SQL_statements
		[if update (column_name ) 
			[{and | or} update (column_name )]... 
			SQL_statements ]...

Parameters

trigger_name

is the name of the trigger. It must conform to the rules for identifiers and be unique in the database. Specify the owner’s name to create another trigger of the same name owned by a different user in the current 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.

table_name

is the name of the table on which to create the trigger. If more than one table of the same name exists in the database, specify the owner’s name. The default value for owner is the current user.

insert, update, delete

can be included in any combination. delete cannot be used with the if update clause.

SQL_statements

specify trigger conditions and trigger actions. 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.

See “Triggers and transactions” for a list of statements that are not allowed in a trigger definition. See “The deleted and inserted logical tables” for information about the deleted and inserted logical tables that can be included in trigger definitions.

if update

is used to test whether the specified column is included in the set list of an update statement or is affected by an insert. 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:

create trigger reminder 
on titles 
for insert, update as 
print "Don't forget to print a report for accounting."

Example 2

Prevents insertion of a new row into titleauthor if there is no corresponding title_id in the titles table:

create trigger t1 
on titleauthor 
for insert as 
if (select count(*) 
    from titles, inserted 
    where titles.title_id = inserted.title_id) = 0
begin 
print "Please put the book's title_id in the 
        titles table first." 
rollback transaction 
end

Example 3

If the pub_id column of the publishers table is changed, make the corresponding change in the titles table:

create trigger t2 
on publishers 
for update as 
if update (pub_id) and @@rowcount = 1
begin 
    update titles 
    set titles.pub_id = inserted.pub_id 
    from titles, deleted, inserted 
    where deleted.pub_id = titles.pub_id 
end

Example 4

Deletes title from the titles table if any row is deleted from titleauthor. If the book was written by more than one author, other references to it in titleauthor are also deleted:

create trigger t3 
on titleauthor 
for delete as 
begin 
    delete titles 
    from titles, deleted 
    where deleted.title_id = titles.title_id 
    delete titleauthor 
    from titleauthor, deleted 
    where deleted.title_id = titleauthor.title_id
    print "All references to this title have been
    deleted from titles and titleauthor." 
end

Example 5

Prevents updates to the primary key on weekends. Prevents updates to the price or advance of a title unless the total revenue amount for that title surpasses its advance amount:

create trigger stopupdatetrig 
on titles 
for update 
as 
if update (title_id) 
  and datename(dw, getdate()) 
  in ("Saturday", "Sunday") 
  begin 
    rollback transaction 
    print "We don't allow changes to"
    print "primary keys on the weekend!" 
  end 
if update (price) or update (advance)
  if (select count(*) from inserted
    where (inserted.price * inserted.total_sales)
    < inserted.advance) > 0
    begin
    rollback transaction
    print "We don't allow changes to price or"
    print "advance for a title until its total"
    print "revenue exceeds its latest advance."
    end

Usage

• 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.

• deleted and inserted tables can be examined by the trigger to determine whether or how the trigger action should be carried out, but the tables themselves cannot be altered by the trigger’s actions.

• deleted tables are used with delete and update; inserted tables, with insert and update. An update is a delete followed by an insert: it affects the deleted table first, and then the inserted table.

Trigger restrictions

• You can create a trigger only in the current database. If you use an owner name to qualify a trigger, you must explicitly qualify the table name the same way. 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 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.

• If a trigger references table names, column names, or view names that are not valid identifiers, you must set quoted_identifier on before the create trigger command and enclose each such name in double quotes. The quoted_identifier option does not need to be on when the trigger fires.

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. In particular, the self_recursion option can be used inside a trigger so that data modifications by the trigger itself can cause the trigger to fire again.

Dropping a trigger

• You must drop and re-create the trigger if you rename any of the objects referenced by the trigger. You can rename a trigger with sp_rename.

• 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:

  • All create commands, including create database, create default, create index, create procedure, create rule, create table, create trigger, and create view

  • All drop commands

  • alter database and alter table

  • truncate table

  • grant and revoke

  • update statistics

  • sp_configure

  • load database and load transaction

  • disk init, disk refit, disk reinit, disk remirror, disk remirror, disk unmirror

  • select into

• If a desired result (such as a summary value) depends on the number of rows affected by a data modification, use @@rowcount to test for multirow data modifications (an insert, delete, or update based on a select statement), and take appropriate actions. Any Transact-SQL statement that does not return rows (such as an if statement) sets @@rowcount to 0, so the test of @@rowcount should occur at the beginning of the trigger.

Inserting and updating triggers

• When an insert or update command executes, Adaptive Server adds rows to both the trigger table and the inserted table at the same time. The rows in the inserted table are always duplicates of one or more rows in the trigger table.

• An update or insert trigger can use the if update command to determine whether the update or insert changed a particular column. if update(column_name) is true for an insert statement whenever the column is assigned a value in the select list or in the values clause. An explicit NULL or a default assigns a value to a column and thus activates the trigger. An implicit NULL, however, does not.

  For example, if you create the following table and trigger:

  create table junk 
  (aaa int null, 
  bbb int not null)
  

  create trigger trigtest on junk
  for insert as 
  if update (aaa) 
      print "aaa updated"
  if update (bbb)
      print "bbb updated"
  

  Inserting values into either column or into both columns fires the trigger for both column aaa and column bbb:

  insert junk (aaa, bbb) 
  values (1, 2)
  

  aaa updated
  bbb updated
  

  Inserting an explicit NULL into column aaa also fires the trigger:

  insert junk 
  values (NULL, 2)
  

  aaa updated
  bbb updated
  

  If there was a default for column aaa, the trigger would also fire.

  However, with no default for column aaa and no value explicitly inserted, Adaptive Server generates an implicit NULL and the trigger does not fire:

  insert junk (bbb) 
  values(2)
  

  bbb updated
  

  if update is never true for a delete statement.

Nesting triggers and trigger recursion

• Adaptive Server allows nested triggers by default. To prevent triggers from nesting, use sp_configure to set the allow nested triggers option to 0 (off), as follows:

  sp_configure "allow nested triggers", 0
  

• Triggers can be nested to a depth of 16 levels. If a trigger changes a table on which there is another trigger, the second trigger fires and can then call a third trigger, and so forth. If any trigger in the chain sets off an infinite loop, the nesting level is exceeded and the trigger aborts, rolling back the transaction that contains the trigger query.

  Since triggers are put into a transaction, a failure at any level of a set of nested triggers cancels the entire transaction: all data modifications are rolled back. Supply your triggers with messages and other error handling and debugging aids to determine where the failure occurred.

• The global variable @@nestlevel contains the nesting level of the current execution. Each time a stored procedure or trigger calls another stored procedure or trigger, the nesting level is incremented. The nesting level is also incremented by one when a cached statement is created. If the maximum of 16 is exceeded, the transaction aborts.

• If a trigger calls a stored procedure that performs actions that would cause the trigger to fire again, the trigger is reactivated only if nested triggers are enabled. Unless there are conditions within the trigger that limit the number of recursions, this causes a nesting-level overflow.

  For example, if an update trigger calls a stored procedure that performs an update, the trigger and stored procedure execute once if allow nested triggers is off. If allow nested triggers is on, and the number of updates is not limited by a condition in the trigger or procedure, the procedure or trigger loop continues until it exceeds the 16-level maximum nesting value.

• By default, a trigger does not call itself in response to a second data modification to the same table within the trigger, regardless of the setting of the allow nested triggers configuration parameter. A set option, self_recursion, enables a trigger to fire again as a result of a data modification within the trigger. For example, if an update trigger on one column of a table results in an update to another column, the update trigger fires only once when self_recursion is disabled, but it can fire up to 16 times if self_recursion is set on. The allow nested triggers configuration parameter must also be enabled in order for self-recursion to take place.

Getting information about triggers

• The execution plan for a trigger is stored in sysprocedures.

• Each trigger is assigned an identification number, which is stored as a new row in sysobjects with the object ID for the table to which it applies in the deltrig column, and also as an entry in the deltrig, instrig, and updtrig columns of the sysobjects row for the table to which it applies.

• To display the text of a trigger, which is stored in syscomments, use sp_helptext.

  If the System Security Officer has reset the allow select on syscomments.text column parameter with sp_configure (as required to run Adaptive Server in the evaluated configuration), you must be the creator of the trigger or a System Administrator to view the text of the trigger through sp_helptext.

• For a report on a trigger, use sp_help.

• For a report on the tables and views that are referenced by a trigger, use sp_depends.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Only a System Security Officer can grant or revoke permissions to create triggers. The Database Owner can create triggers on any user table. Users can create triggers only on tables that they own.

Permission to issue the create trigger command is granted to users by default.

When the System Security Officer revokes permission for a user to create triggers, a revoke row is added in the sysprotects table for that user. To grant permission to that user to issue create trigger, issue two grant commands: the first command removes the revoke row from sysprotects; the second inserts a grant row. If permission to create triggers is revoked, the user cannot create triggers even on tables that the user owns. Revoking permission to create triggers from a user affects only the database where the revoke command was issued.

Permissions on objects at trigger creation When you create a trigger, Adaptive Server makes no permission checks on objects such as tables or views that the trigger references. Therefore, you can create a trigger successfully, even though you do not have access to its objects. All permission checks occur when the trigger fires.

Permissions on objects at trigger execution When the trigger executes, permission checks on its objects depend on whether the trigger and its objects are owned by the same user.

• If the trigger and its objects are not owned by the same user, the user who caused the trigger to fire must have been granted direct access to the objects. For example, if the trigger performs a select from a table the user cannot access, the trigger execution fails. In addition, the data modification that caused the trigger to fire is rolled back.

• If a trigger and its objects are owned by the same user, special rules apply. The user automatically has implicit permission to access the trigger’s objects, even though the user cannot access them directly. A detailed description of the rules for implicit permissions is discussed in the System Administration Guide.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also

Commands alter table, create procedure, drop trigger, rollback trigger, set

System procedures sp_commonkey, sp_configure, sp_depends, sp_foreignkey, sp_help, sp_helptext, sp_primarykey, sp_rename, sp_spaceused

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF