create trigger

Description

Creates a trigger, which is 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} | instead of {insert, update, delete}}
	[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, which 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.

for | instead of

for – used before insert, delete, or update to indicate what you are creating the trigger for.

instead of – creates and fills the inserted and deleted pseudo tables, which are used in the trigger to examine the rows that would have been modified by the original insert, delete, or update query.

insert, update, delete

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

SQL_statements

specifies 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

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

Example 6

Uses instead of triggers to update union views:

create table EmployeeWest (
    
    empid                          int primary key,
    empname                        varchar(30),
    empdob                         datetime,
    region                         char(5)
        constraint region_chk
           check (region='West'))

create table EmployeeEast (
    empid                          int primary key,
    empname                        varchar(30),
    empdob                         datetime,
    region                         char(5)
        constraint region_chk
           check (region='East’))

create view Employees as
    select * from EmployeeEast
    union all
    select * from EmployeeWest

create trigger EmployeesInsertTrig on Employees
instead of insert as
begin
    
    insert into EmployeeEast select * from inserted where region = "East"

    insert into EmployeeWest select * from inserted where region = "West"
end

--will insert the data into the EmployeeEast table
insert into Employees values (10, 'Jane Doe', '11/11/1967', 'East')

--will insert the data into the EmployeeWest table
insert into Employees values (11, 'John Smith', '01/12/1977', 'West')

--will insert multiple rows into EmployeeEast and 
--EmployeeWest tables. Employee2 table includes employees 
--from both East and West.
insert into Employees select * from Employee2

Example 7

Uses instead of triggers to implement encrypted column support, storing data in the database in encrypted form without changing applications (the user-defined functions, my_encrypt and my_decrypt, perform the encryption and decryption operations on the data):

CREATE TABLE Employee_t (id int PRIMARY KEY, name varchar(20), 
            salary binary (64))
--where the id and name columns are stored unencrypted, salary is
--encrypted and id is a primary key.

create view employee_v as select id, name, my_decrypt (salary)
from employee_t

CREATE TRIGGER EmployeeInsert
ON employee_v
INSTEAD OF INSERT
AS
BEGIN
    INSERT employee_t SELECT id, name, my_encrypt (salary)
    FROM inserted
END

CREATE TRIGGER employeeUpdate
ON employee_v
INSTEAD OF UPDATE
AS
BEGIN
    DELETE FROM employee_t WHERE id IN (SELECT id FROM deleted)
    INSERT employee_t SELECT id, name, my_encrypt (salary)
    FROM inserted
END

CREATE TRIGGER employeeDelete
ON employee_v
INSTEAD OF DELETE
AS
BEGIN
    DELETE FROM employee_t WHERE id IN (SELECT id FROM deleted)
END

Usage


Triggers and referential integrity


The deleted and inserted logical tables


Trigger restrictions


Triggers and performance


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


Dropping a trigger


Actions that do not cause triggers to fire


Triggers and transactions


Inserting and updating triggers


Nesting triggers and trigger recursion


instead of and for triggers

Restrictions for instead of:


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 has implicit permission to create a trigger on any user table. Users can create triggers only on tables that they own.

The system security officer may revoke user permission to create triggers. Revoking permission to create triggers affects only the database in which the systems security officer issues the revoke command. Permission to run the create trigger command is restored to the users whose permission was revoked when the system security officer explicitly grants them create trigger permission.

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 successfully create a trigger, 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.

Permissions for instead of and for triggers instead of triggers have the same permission requirements as for triggers: to create a view with instead of triggers, permission for insert/update/delete for the view, not the underlying tables, must be granted to the user.

Auditing

Values in event and extrainfo columns of sysaudits are:

Event

Audit option

Command or access audited

Information in extrainfo

12

create

create trigger

  • Roles – current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if set proxy is in effect

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