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

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 specfies 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:

NOTES

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

EXAMPLE

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:

CREATE TRIGGER BUG_UPDATE_TRIGGER 
  AFTER UPDATE OF STATUS, PRIORITY ON BUG_INFO 
  REFERENCING OLDROW, NEWROW 
  FOR EACH ROW 
  
IMPORT   
import java.sql.* ; 
BEGIN 
try  
{      
     // column number of STATUS is 10 
     String  old_status, new_status; 
  
     old_status = (STRING) OLDROW.GetValue(10, CHAR); 
     new_status = (STRING) NEWROW.GetValue(10, CHAR); 
  
     if ((old_status.CompareTo("OPEN") == 0) &&  
     (new_status.CompareTo("FIXED") == 0)) 
  
     { 
          //  If STATUS has changed from OPEN to FIXED 
          //  increment the bugs_fixed_cnt by 1 in the  
          //  row corresponding to current month   
          //  and current year 
  
          SQLIStatement  update_stmt ( 
          " update BUG_STATUS set bugs_fixed_cnt = bugs_fixed_cnt + 1 " 
          " where  month = ?  and year = ?" 
          ); 
  
          Integer  current_month = 10; 
          Integer  current_year  = 1997; 
  
          update_stmt.SetParam(1, current_month); 
          update_stmt.SetParam(2, current_year); 
          update_stmt.Execute(); 
  
          SQLIStatement  insert_stmt ( 
          " insert into BUG_SUMMARY values (?,?,?)" 
          ); 
  
          //  Column numbers for bug_id, priority, reported_on 
          //  and fixed_on are 1, 2, 5, and 6 
          String  bug_id,priority; 
          Date    reported_on, fixed_on; 
 bug_id = (String) NEWROW.GetValue(1, CHAR); 
          priority = (String) NEWROW.GetValue(2, CHAR); 
          reported_on = (Date) NEWROW.GetValue(5,DATE); 
          fixed_on = (Data) NEWROW.GetValue(6, DATE); 
           
          Integer turn_around_time = fixed_on - reported_on; 
  
           insert_stmt.SetParam(1, bug_id); 
          insert_stmt.SetParam(2, priority); 
          insert_stmt.SetParam(3, turn_around_time); 
          insert_stmt.Execute(); 
          } 
     // If PRIORITY has changed to URGENT, 
     // increment the bugs_escalated by 1 in the month field . 
  
     String  old_priority, new_priority; 
     old_priority = (String) OLDROW.GetValue(2, CHAR); 
     new_priority = (String) NEWROW.GetValue(2, CHAR); 
  
     if ((new_priority.CompareTo("URGENT")==0)  &&  
     (old_priority.CompareTo("URGENT") != 0)) 
     { 
          //  If PRIORITY  has changed to URGENT increment the 
          //  bugs_escalated  by 1 in the row corresponding to 
          //  current month and current year 
          SQLIStatement  update_stmt ( 
          " update BUG_STATUS 
          set bugs_escalated_cnt = bugs_escalated_cnt + 1 " 
          " where  month = ?  and year = ?" 
          ); 
  
          Integer  current_month = 10; 
          Integer  current_year  = 1997; 
  
          update_stmt.SetParam(1, current_month); 
          update_stmt.SetParam(2, current_year); 
          update_stmt.Execute(); 
     } 
  
} 
catch (SQLException e) 
{ 
     // Log the exception message from e. 
     SQLException  sqle = new SQLException("UPDATE_BUG_TRIGGER failed"); 
     throw sqle; 
} 
END 

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

DROP TRIGGER Statement


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