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

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

• To avoid seeing unexpected results due to changes in settings, run set rowcount 0 as your initial statement before executing create trigger. The scope of set is limited to only the 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.

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

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

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

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

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

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

• You can interleave nesting instead of and for triggers. For example, an update statement on a view with an instead of update trigger causes the trigger to execute. If the trigger contains a SQL statement updating a table with a for trigger defined on it, that trigger fires. The for trigger may contain a SQL statement that updates another view with an instead of trigger that then executes, and so forth.

• instead of and for triggers have different recursive behaviors. for triggers support recursion, while instead of triggers do not. If an instead of trigger references the same view on which the trigger was fired, the trigger is not called recursively. Rather, the triggering statement applies directly to the view; in other words, the statement is resolved as modifications against the base tables underlying the view. In this case, the view definition must meet all restrictions for an updatable view. If the view is not updatable, an error is raised.

  For example, if a trigger is defined as an instead of update trigger for a view, the update statement executed against the same view within the instead of trigger does not cause the trigger to execute again. The update exercised by the trigger is processed against the view, as though the view did not have an instead of trigger. The columns changed by the update must be resolved to a single base table.

Restrictions for instead of:

• 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 quotation marks. The quoted_identifier option does not need to be on when the trigger fires; bracketed identifiers also work.

• Using the set cursor rows command with client cursors, cursors declared through Open Client calls, or Embedded SQL™, may prevent positioned delete and update from firing an instead of trigger. A positioned update statement is a SQL update statement that contains the where current of <cursorname> clause to update only the row upon which the cursor, <cursorname>, is currently positioned.

• Joins are not allowed in searched delete and update statements that would fire an instead of trigger.

• positioned delete and update on cursors defined with joins does not fire an instead of trigger.

  A positioned delete (or positioned update) is a SQL delete (or update) statement containing a where current of <cursorname> clause to delete (or update) only the row upon which the cursor, <cursorname>, is currently positioned.

• For positioned delete and update statements that fire an instead of trigger, the instead of trigger must exist when the cursor is declared.

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

• 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. See the detailed description of the rules for implicit permissions in the System Administration Guide.

Auditing

Values in event and extrainfo columns of sysaudits are:

See also