Release 10.1A: OpenEdge Data Management:
SQL Reference

> ## CREATE TRIGGER

> 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

>     CREATE TRIGGER [ owner_name.]trigname
>       { BEFORE | AFTER }
>       { INSERT | DELETE | UPDATE [ OF ( column_name [ , ... ] ) ] }
>       ON table_name
>       [ REFERENCING { OLDROW [ ,NEWROW ] | NEWROW [ ,OLDROW ] } ]
>       [ FOR EACH { ROW | STATEMENT } ]
>       [ IMPORT
>           java_import_clause ]
>         BEGIN
>               java_snippet
>         END

> ``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 uses `trigname` 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` or `AFTER` the invoking `INSERT`, `UPDATE`, or `DELETE` 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. If `UPDATE` is the triggering statement and does not include the optional column list, then any `UPDATE` 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 in `java_snippet` uses the getValue method of the `NEWROW` and `OLDROW` 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 specifies the `FOR EACH ROW` clause.

> The meaning of the `OLDROW` and `NEWROW` arguments of the `REFERENCING` clause depends on whether the trigger event is `INSERT`, `UPDATE`, or `DELETE`:

> - `INSERT`...`REFERENCING` `NEWROW` means the triggered action can access values of columns of each row inserted. SQL passes the column values specified by the `INSERT` 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 both `NEWROW` and `OLDROW` in the `REFERENCING` 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 the `UPDATE` statement.
> - The trigger action time (`BEFORE` or `AFTER`) does not affect the meaning of the `REFERENCING` 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 the `FOR` `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 the `FOR` `EACH` `ROW` clause if it also includes a `REFERENCING` 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
> END`

> Denotes 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` and `END` keywords must be uppercase and on separate lines.

> #### Notes

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

> The following 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:

> Example

> The following code segment illustrates how to use the `CREATE TRIGGER` statement:

>     CREATE TRIGGER TRG_TEST04 BEFORE INSERT ON tst_trg_01
>     REFERENCING NEWROW
>     FOR EACH ROW
>     IMPORT
>        import java.sql.*;
>     BEGIN
>        //Inserting Into tst_trg_03
>        Integer new_value=newInteger(0);
>        new_value=(Integer)NEWROW.getValue (1,INTEGER);
>        SQLIStatement insert_tst3=new SQLIStatement ("INSERT INTO tst_trg_03 values
>     (?)");
>        insert_tst3.setParam (1,new_value);
>        insert_tst3.execute();
>     END

> The following code segment illustrates how to set values for a new row in the `CREATE TRIGGER` statement:

>     CREATE TRIGGER trg1403
>     BEFORE INSERT ON tbl1401
>     REFERENCING NEWROW
>     FOR EACH ROW
>     IMPORT
>     import java.sql.* ;
>     BEGIN
>     INTEGER n2 = new INTEGER(12345);
>     NEWROW.setValue(2, n2);
>     END

> ##### Authorization

> Must have the `DBA` privilege or `RESOURCE` privilege

> ##### Related statements

> DROP TRIGGER

> Note: For more information on creating and using triggers, see OpenEdge Data Management: SQL Development .

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095