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


Triggers and referential integrity


The deleted and inserted logical tables


Trigger restrictions


Triggers and performance


Setting options within triggers


Dropping a trigger


Actions that do not cause triggers to fire


Triggers and transactions


Inserting and updating triggers


Nesting triggers and trigger recursion


Getting information about triggers

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.

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