Contents Index CREATE TABLE statement CREATE TRIGGER statement [SQL Remote]

ASA SQL Reference
  SQL Statements

CREATE TRIGGER statement

Description 

Use this statement to create a new trigger on a table.

Syntax 

CREATE TRIGGER trigger-name trigger-time trigger-event [, trigger-event,...]
[ ORDER integer ] ON table-name
[ REFERENCING [ OLD AS old-name ]
                            [ NEW AS new-name ] ]
                            [ REMOTE AS remote-name ] ]
[ FOR EACH { ROW | STATEMENT } ]
[ WHEN ( search-condition ) ]
  compound-statement

trigger-time : BEFORE | AFTER | RESOLVE

trigger-event :
DELETE | INSERT | UPDATE | UPDATE OF column-list

Parameters 

Trigger events    Triggers can be fired by one or more of the following events:

You may write separate triggers for each event that you need to handle or, if you have some shared actions and some actions that depend on the event, you can create a trigger for all events and use an IF statement to distinguish the action taking place.

For more information, see IF statement.

trigger-time    Row-level triggers can be defined to execute BEFORE or AFTER the insert, update, or delete. Statement-level triggers execute AFTER the statement. The RESOLVE trigger time is for use with SQL Remote: it fires before row-level UPDATE or UPDATE OF column-lists only.

BEFORE UPDATE triggers fire any time an UPDATE occurs on a row, whether or not the new value differs from the old value. AFTER UPDATE triggers fire only if the new value is different from the old value.

FOR EACH clause    To declare a trigger as a row-level trigger, use the FOR EACH ROW clause. To declare a trigger as a statement-level trigger, you can either use a FOR EACH STATEMENT clause or omit the FOR EACH clause. For clarity, it is recommended that you enter the FOR EACH STATEMENT clause if declaring a statement-level trigger.

ORDER clause    Triggers of the same type (insert, update, or delete) that fire at the same time (before, after, or resolve) can use the ORDER clause to determine the order that the triggers are fired.

REFERENCING clause    The REFERENCING OLD and REFERENCING NEW clauses allow you to refer to the inserted, deleted or updated rows. For the purposes of this clause, an UPDATE is treated as a delete followed by an insert.

An INSERT takes the REFERENCING NEW clause, which represents the inserted row. There is no REFERENCING OLD clause.

A DELETE takes the REFERENCING OLD clause, which represents the deleted row. There is no REFERENCING NEW clause.

An UPDATE takes the REFERENCING OLD clause, which represents the row before the update, and it takes the REFERENCING NEW clause, which represents the row after the update.

The meaning of REFERENCING OLD and REFERENCING NEW differs, depending on whether the trigger is a row-level or a statement-level trigger. For row-level triggers, the REFERENCING OLD clause allows you to refer to the values in a row prior to an update or delete, and the REFERENCING NEW clause allows you to refer to the inserted or updated values. The OLD and NEW rows can be referenced in BEFORE and AFTER triggers. The REFERENCING NEW clause allows you to modify the new row in a BEFORE trigger before the insert or update operation takes place.

For statement-level triggers, the REFERENCING OLD and REFERENCING NEW clauses refer to declared temporary tables holding the old and new values of the rows. The default names for these tables are deleted and inserted.

The REFERENCING REMOTE clause is for use with SQL Remote. It allows you to refer to the values in the VERIFY clause of an UPDATE statement. It should be used only with RESOLVE UPDATE or RESOLVE UPDATE OF column-list triggers.

WHEN clause    The trigger fires only for rows where the search-condition evaluates to true. The WHEN clause can be used only with row level triggers.

Usage 

The CREATE TRIGGER statement creates a trigger associated with a table in the database, and stores the trigger in the database.

The trigger is declared as either a row-level trigger, in which case it executes before or after each row is modified, or as a statement-level trigger, in which case it executes after the entire triggering statement is completed.

Permissions 

Must have RESOURCE authority and have ALTER permissions on the table, or must be the owner of the table or have DBA authority. CREATE TRIGGER puts a table lock on the table, and thus requires exclusive use of the table.

Side effects 

Automatic commit.

See also 

BEGIN statement

CREATE PROCEDURE statement

CREATE TRIGGER statement [T-SQL]

DROP statement

Using Procedures, Triggers, and Batches

Standards and compatibility 
Example 

The first example creates a row-level trigger. When a new department head is appointed, update the manager_id column for employees in that department.

CREATE TRIGGER tr_manager
BEFORE UPDATE OF dept_head_id
ON department
REFERENCING OLD AS old_dept NEW AS new_dept
FOR EACH ROW
BEGIN
   UPDATE employee
   SET employee.manager_id=new_dept.dept_head_id
   WHERE employee.dept_id=old_dept.dept_id
END

The next example, which is more complex, deals with a statement-level trigger. First, create a table as follows:

CREATE TABLE "DBA"."t0"
(
   "id"           integer NOT NULL,
   "times"        timestamp NULL DEFAULT current timestamp,
   "remarks"            text NULL,
   PRIMARY KEY ("id")
)

Next, create a statement-level trigger for this table:

create trigger DBA."insert-st" after insert order 4 on
DBA.t0
referencing new as new_name
for each statement
begin
  declare @id1 integer;
  declare @times1 timestamp;
  declare @remarks1 long varchar;

  declare @err_notfound exception for sqlstate value '02000';

//declare a cursor for table new_name
  declare new1 cursor for
   select id,times,remarks from
      new_name;
  open new1;
 //Open the cursor, and get the value
  LoopGetRow:
  loop
      fetch next new1
   into @id1, @times1,@remarks1;

      if sqlstate = @err_notfound then
   leave LoopGetRow
      end if;

      //print the value or for other use
      Print (@remarks1);

  end loop LoopGetRow;
  close new1

end

Contents Index CREATE TABLE statement CREATE TRIGGER statement [SQL Remote]