Defining a Trigger Event and Action

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > CREATE TRIGGER >

Defining a Trigger Event and Action

This syntax defines the event and action of a trigger on a table or on a view.

Trigger on a Table:
 
|--+-+-DELETE----------------------------+--ON--table--| DELETE and SELECT Subclauses |-+--|
   | |  (1)                              |                                              |
   | '--------SELECT--+----------------+-'                                              |
   |                  |     .-,------. |                                                |
   |                  |     V        | |                                                |
   |                  '-OF----column-+-'                                                |
   +-UPDATE--+----------------+--ON--table--| UPDATE Subclauses |-----------------------+
   |         |     .-,------. |                                                         |
   |         |     V        | |                                                         |
   |         '-OF----column-+-'                                                         |
   |                                          (2)                               (3)     |
   '-INSERT--ON--table--+-| NEW Declaration |-------| Correlated Table Action |------+--'
                        |                   (4)                                      |
                        '-| Action Clause |------------------------------------------'
 
DELETE and SELECT Subclauses:
 
                       (4)
|--+-| Action Clause |------------------------------------------+--|
   |                     (5)                               (3)  |
   '-| OLD Declaration |-------| Correlated Table Action |------'
 
UPDATE Subclauses:
 
                       (4)
|--+-| Action Clause |--------------------------------------------------------------------+--|
   |                     (5)                               (3)                            |
   +-| OLD Declaration |-------| Correlated Table Action |--------------------------------+
   |                     (5)                       (2)                               (3)  |
   '-| OLD Declaration |-------| NEW Declaration |-------| Correlated Table Action |------'
 
Trigger on a View:
 
    (1)
|--------------------------------------------------------------->
 
>--+-INSERT ON--view--+------------------------------+------------------------+-->
   |                  '-REFERENCING NEW--+----+--new-'                        |
   |                                     '-AS-'                               |
   +-DELETE ON--view--+-------------------------------+-----------------------+
   |                  '-REFERENCING--OLD--+----+--old-'                       |
   |                                      '-AS-'                              |
   '-UPDATE ON--view--+-----------------------------------------------------+-'
                      +-REFERENCING--OLD--+----+--old--+------------------+-+
                      |                   '-AS-'       '-NEW--+----+--new-' |
                      |                                       '-AS-'        |
                      '-REFERENCING--NEW--+----+--new--+------------------+-'
                                          '-AS-'       '-OLD--+----+--old-'
                                                              '-AS-'
 
                                                 (6)
>--FOR EACH ROW--| INSTEAD OF Triggered Action |----------------|

Notes:

Dynamic Server only
See page REFERENCING Clause for Insert
See page Correlated Table Action
See page Action Clause
See page REFERENCING Clause for Update
See page INSTEAD OF Triggers on Views (IDS)

Element	Description	Restrictions	Syntax
column	The name of a column in the triggering table	Must exist	Identifier, p. Identifier
new, old	Old or new correlation name that you declare here	Unique in this trigger	Identifier, p. Identifier
table, view	Name or synonym of the triggering table or view. The table or view can include an owner. qualifier.	Must exist in the current database	Database Object Name, p. Database Object Name

The left-hand portion of the main diagram (including the table or view) defines the trigger event (sometimes called the triggering event). The rest of the diagram declares correlation names and defines the trigger action (sometimes called the triggered action). (For triggers on tables, see Action Clause and Correlated Table Action. For INSTEAD OF triggers on views, see The Action Clause of INSTEAD OF Triggers (IDS).)