Progress
SQL-92
Guide and Reference
CREATE TRIGGER Statement
Creates a trigger for the specified table. A trigger is a special type of automatically executed stored procedure that helps ensure referential integrity for a database.
Triggers contain Java source code that can use SQL Java classes to carry out database operations. Triggers are automatically activated when an INSERT, UPDATE, or DELETE statement changes the trigger's target table. The Java source code details what actions the trigger takes when it is activated.
SYNTAX
owner_name
Specifies the owner of the trigger. If the name is different from the user name of the user executing the statement, then the user must have DBA privileges.
trigname
Names the trigger.
DROP TRIGGER
statements specify the trigger name defined here. SQL also usestrigname
in the name of the Java class that it creates from the Java snippet.BEFORE | AFTER
Denotes the trigger action time. The trigger action time specifies whether the triggered action, implemented by java_snippet, executes
BEFORE
orAFTER
the invokingINSERT
,UPDATE
, orDELETE
statement.INSERT |DELETE |UPDATE [ OF column_name [ , ...] ]
Denotes the trigger event. The trigger event is the statement that activates the trigger.
If
UPDATE
is the triggering statement, this clause can include an optional column list. Only updates to any of the specified columns will activate the trigger. IfUPDATE
is the triggering statement and does not include the optional column list, then anyUPDATE
on the table will activate the trigger.ON table_name
Identifies the name of the table where the trigger is defined. A triggering statement that specifies table_name causes the trigger to execute. table_name cannot be the name of a view.
REFERENCING OLDROW [ , NEWROW ] | NEWROW [ , OLDROW ]
Provides a mechanism for SQL to pass row values as input parameters to the stored procedure implemented by
java_snippet
. The code injava_snippet
uses the getValue method of theNEWROW
andOLDROW
objects to retrieve values of columns in rows affected by the trigger event and store them in procedure variables. This clause is allowed only if the trigger specfies theFOR EACH ROW
clause.The meaning of the
OLDROW
andNEWROW
arguments of theREFERENCING
clause depends on whether the trigger event isINSERT
,UPDATE
, orDELETE
:
INSERT
...REFERENCING
NEWROW
means the triggered action can access values of columns of each row inserted. SQL passes the column values specified by theINSERT
statement.INSERT
...REFERENCING
OLDROW
is meaningless, since there are no existing values for a row being inserted.INSERT
...REFERENCING
OLDROW
generates a syntax error.UPDATE
...REFERENCING
OLDROW
means the triggered action can access the values of columns, before they are changed, of each row updated. SQL passes the column values of the row as it exists in the database before the update operation.DELETE
...REFERENCING
OLDROW
means the triggered action can access values of columns of each row deleted. SQL passes the column values of the row as it exists in the database before the delete operation.DELETE
...REFERENCING
NEWROW
is meaningless, since there are no new existing values to pass for a row being deleted.DELETE
...REFERENCING
OLDROW
generates a syntax error.UPDATE
is the only triggering statement that allows bothNEWROW
andOLDROW
in theREFERENCING
clause.UPDATE
...REFERENCING
NEWROW
means the triggered action can access the values of columns, after they are changed, of each row updated. SQL passes the column values specified by theUPDATE
statement.NOTES
- The trigger action time (
BEFORE
orAFTER
) does not affect the meaning of theREFERENCING
clause. For instance,BEFORE
UPDATE
...REFERENCING
NEWROW
still means the values of columns after they are updated will be available to the triggered action.- The
REFERENCING
clause generates an error if the trigger does not include theFOR
EACH
ROW
clause.FOR EACH { ROW | STATEMENT }
Controls the execution frequency of the triggered action implemented by java_snippet.
FOR
EACH
ROW
means the triggered action executes once for each row being updated by the triggering statement.CREATE
TRIGGER
must include theFOR
EACH
ROW
clause if it also includes aREFERENCING
clause.
FOR
EACH
STATEMENT
means the triggered action executes only once for the whole triggering statement.FOR
EACH
STATEMENT
is the default.IMPORT java_import_clause
Specifies standard Java classes to import. The
IMPORT
keyword must be uppercase and on a separate line.BEGIN
java_snippet
ENDDenotes the body of the trigger or the triggered action. The body contains the Java source code that implements the actions to be completed when a triggering statement specifies the target table. The Java statements become a method in a class that SQL creates and submits to the Java compiler.
The
BEGIN
andEND
keywords must be uppercase and on separate lines.NOTES
EXAMPLE
- Triggers can take action on their own table so that they invoke themselves. SQL limits such recursion to five levels.
- You can you have multiple triggers on the same table. Multiple UPDATE triggers on the same table must specify different columns. SQL-92 executes all triggers applicable to a given combination of table, trigger event, and action time.
- The actions carried out by a trigger can fire another trigger. When this happens, the other trigger's actions execute before the rest of the first trigger finishes executing.
- If a constraint and trigger are both invoked by a particular SQL statement, SQL checks constraints first, so any data modification that violates a constraint does not also fire a trigger.
- To modify an existing trigger, you must delete it and issue another
CREATE TRIGGER
statement. You can query the systrigger system table for information about the trigger before you delete it.This example illustrates an UPDATE trigger on a table called BUG_INFO. If the STATUS or PRIORITY fields are modified, the trigger modifies the BUG_SUMMARY and BUG_STATUS tables appropriately, based on defined conditions:
AUTHORIZATION
Must have the DBA privilege or RESOURCE privilege
SQL COMPLIANCE
SQL-92, ODBC Core SQL grammar
ENVIRONMENT
Embedded SQL, interactive SQL, ODBC applications, JDBC applications
RELATED STATEMENTS
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |