create trigger


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.


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} 
		[if update (column_name ) 
			[{and | or} update (column_name )]...] 
		[if update (column_name ) 
			[{and | or} update (column_name )]... 
			SQL_statements ]...



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.


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.


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


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
print "Please put the book's title_id in the 
        titles table first." 
rollback transaction 

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
    update titles 
    set titles.pub_id = inserted.pub_id 
    from titles, deleted, inserted 
    where deleted.pub_id = titles.pub_id 

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

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 
if update (title_id) 
  and datename(dw, getdate()) 
  in ("Saturday", "Sunday") 
    rollback transaction 
    print "We don't allow changes to"
    print "primary keys on the weekend!" 
if update (price) or update (advance)
  if (select count(*) from inserted
    where (inserted.price * inserted.total_sales)
    < inserted.advance) > 0
    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."


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


ANSI SQL – Compliance level: Transact-SQL extension.


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