create trigger

Syntax

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

Parameters

• create – creates a trigger if one does not already exist. 
• or replace – re-creates an existing trigger. Use this clause to change the definition of a trigger. When specifying or replace, auditing options on the trigger are not dropped. If there is no existing trigger with the name you enter, a new one is created and the old trigger remains. This is in conjunction with multiple triggers.
• 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.

  The name of the trigger is not changed when the trigger definition is replaced. The name of the new trigger definition must match the old name to be replaced. If the trigger name differs from any existing trigger, a new trigger is created and the old trigger is not dropped .

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

  You cannot change the name of the table when a trigger is replaced. If an existing trigger is modified to associate the trigger with another table, then an error is raised indicating that the trigger already exists on another table and cannot be replaced.

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

  You cannot change an "instead of" trigger to a "for" trigger, and vice versa.

• insert, update, delete – can be included in any combination. delete cannot be used with the if update clause.

  You can change these actions when you use the or replace clause. For example, if the old trigger definition specifies all clauses, the replacement definition can specify all clauses, or a combination of the actions.

• order integer – specifies a partial or full ordering of trigger firing:
  • Full ordering occurs when you create all the triggers using the order clause.
  • Partial ordering occurs if you do not specify the order clause on some of the triggers. Triggers without the order clause implicitly take order number 0 and do not have a defined order, except that they fire after those triggers created using order.
• 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.

  You can change trigger conditions and actions when the trigger definition is replaced.

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

  You can drop or add the if update and change the column name referenced by this clause.

• order integer – the order of the trigger firing can also be changed when the trigger definition is replaced.

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

• Example 8 – Creates a trigger that prints a message when anyone tries to insert or update data in the titles table:

  create trigger reminder    
  on titles    
  for insert, update as    
  print "Don't forget to print a report for accounting." 
  
  select object_id("reminder")      
  -----------     
  1312004674   

  The next command changes the message of the printed trigger when anyone tries to update data in the titles table using the or replace clause:

  create or replace trigger reminder    
  on titles    
  for update as    
  print "Don't forget to give a report to accounting." 
  
  select object_id("reminder")      
  -----------     
  1312004674   

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.

• In versions earlier than SAP ASE 16.0, consecutive create trigger commands dropped the old trigger and replaced it with a new trigger definition. However, the auditing options for the trigger were also dropped. Using the optional or replace clause, the definition is replaced, and auditing options are preserved.

• In SAP ASE version 16.0 and later, when there are multiple triggers, specifying create without or replace raises an error if the trigger name is same. If you specify a different trigger name, a new trigger is created and the old trigger remains.

• In versions of SAP ASE earlier than 16.0, if an existing trigger was replaced with the new trigger definition by specifying create without or replace, the name of the new trigger did not need to be same as the name of the old trigger name.

• You can only use the order integer clause with for {insert | update | delete}; you cannot use it with instead of {insert | update | delete} triggers.

• If you use a duplicate number for order, SAP ASE reports an error. order numbers need not be consecutive; in fact, nonconsecutive numbers might be preferable, as they allow you to insert new triggers into the middle of an order.

See also sp_commonkey, sp_configure, sp_depends, sp_foreignkey, sp_help, sp_helptext, sp_primarykey, sp_rename, sp_spaceused in Reference Manual: Procedures.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Permissions on objects at trigger creation – when you create a trigger, the SAP ASE 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 for instead ofand for triggers instead of – 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.

Any user who impersonates the trigger owner through an alias or setuser cannot replace the trigger.

The following describes permission checks for create trigger that differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are: