![]() |
SQL-J Language Reference
|
Reference Manual |
CREATE TRIGGER statementA trigger defines a set of actions that are executed when a database event occurs on a specified table. A database event is a delete, insert, or update operation. For example, if you define a trigger for a delete on a particular table, the trigger's action occurs whenever someone deletes a row or rows from the table. Along with constraints, triggers can help enforce data integrity rules with actions such as cascading deletes or updates. Triggers can also perform a variety of functions such as issuing alerts, updating other tables, sending e-mail, and other useful actions. You can define any number of triggers for a single table, including multiple triggers on the same table for the same event. You can create a trigger in any schema except SYS. The trigger need not reside in the same schema as the table on which it is defined. You cannot create triggers on any table in the SYS schema. Syntax
CREATE TRIGGER TriggerName ReferencingClause
REFERENCING Before or After: When Triggers FireTriggers are either before or after triggers (in Syntax above, see the second line):
Insert, Delete, or Update: What Causes the Trigger to FireA trigger is fired by one of the following database events, depending on how you define it (in Syntax above, see the third line): You can define any number of triggers for a given event on a given table. For update, you can specify columns. Referencing Old and New Values: The Referencing ClauseMany trigger actions need to refer to data that is currently being changed by the database event that caused them to fire. The trigger action may need to refer to the old (pre-change, or "before") values or the new (post-change or "after") values. Cloudscape provides you with a number of ways to refer to data that is currently being changed by the database event that caused the trigger to fire. The easiest way to refer to the changed data in the trigger action is use the transition variables or transition tables. For row triggers, the transition variables NEW and OLD refer to the after and before image of the changes made to a row being modified by the current event. For example, you can refer to the transition variable "OLD" in the trigger action: DELETE FROM HotelAvailability WHERE hotel_id = OLD.hotel_id The referencing clause allows you to provide a correlation name or alias for these transition variables by specifying OLD/NEW AS correlationName if you do not wish to use the default variable names. For example, if you add the following clause to the trigger definition (the word "row" is optional): REFERENCING OLD ROW AS DELETEDROW you can then refer to this correlation name in the trigger action: DELETE FROM HotelAvailability WHERE hotel_id = DELETEDROW.hotel_id The OLD and NEW transition variables map to a java.sql.ResultSet with a single row. NOTE: Only row triggers (see Statement vs. Row Triggers) can use the transition variables. INSERT row triggers cannot reference an OLD row. DELETE row triggers cannot reference a NEW row. For statement triggers, transition tables serve as a table identifier for the trigger action or the trigger qualification. The default identifier for transition tables are NEW and OLD for the after and before row sets, respectively; you must specify the word TABLE after the keyword NEW or OLD. For example, you can refer to the transition table "OLD" in the trigger action: DELETE FROM HotelAvailability WHERE hotel_id IN You can supply an alternate identifier to be used in place of the default identifiers by adding a referencing clause that specifies OLD/NEW TABLE AS identifier. For example: REFERENCING OLD TABLE AS DeletedHotels allows you to use that new identifier (DeletedHotels) in the trigger action: DELETE FROM HotelAvailability WHERE hotel_id IN The old and new transition tables map to a java.sql.ResultSet with cardinality equivalent to the number of rows affected by the triggering event. NOTE: Only statement triggers (see Statement vs. Row Triggers) can use the transition tables. INSERT statement triggers cannot reference an OLD table. DELETE statement triggers cannot reference a NEW table. The referencing clause can designate only one new correlation or identifier and only one old correlation or identifier. Row triggers cannot designate an identifier for a transition table and statement triggers cannot designate a correlation for transition variables. NOTE: There are other ways to reference old and new values; see Trigger Execution Context and Transition Row VTIs. Statement vs. Row TriggersBy default, a trigger is a statement trigger unless you specify that it is a row trigger (in Syntax above, see the sixth line):
NOTE: An update that sets a column value to the value that it originally contained (for example, UPDATE T SET C = C) causes a row trigger to fire, even though the value of the column is the same as it was prior to the triggering event. TriggerActionThe action defined by the trigger is called the trigger action (in Syntax above, see the last line). It can be any valid SQL-J statement, including one with Java method calls. It has the following limitations:
For more information on trigger actions, see Programming Trigger Actions in the Cloudscape Developer's Guide. Order of ExecutionWhen a database event occurs that fires a trigger, Cloudscape performs actions in this order:
When multiple triggers are defined for the same database event for the same table for the same trigger time (before or after), triggers are fired in the order in which they were created. Trigger Execution ContextYou can access information about a trigger's context from a new structure, COM.cloudscape.database.TriggerExecutionContext. You obtain it by calling COM.cloudscape.database.Factory.getTriggerExecutionContext() from within a TriggerAction (or from within a Java method called by a TriggerAction). For example, within a Java method (executed by a TriggerAction): TriggerExecutionContext context = COM.cloudscape.database.Factory.getTriggerExecutionContext(); ResultSet oldrow = context.getOldRow(); CREATE TRIGGER . . . In addition to access to before and after rows, the TriggerExecutionContext provides the following information:
For complete information on how to use this object, see the Javadoc for COM.cloudscape.database.TriggerExecutionContext. Transition Row VTIsThe class COM.cloudscape.vti.TriggerOldTransitionRows is a VTI wrapper around a TriggerExecutionContext.getOldRowSet(). Selecting an instantiation of this VTI is the equivalent of the OLD TABLE transition table. The only difference is that this VTI is accessible from within an SQL-J statement executed by a Java method called by the trigger action; the OLD transition table is accessible only by the trigger action's SQL. For example, the following two trigger actions are identical: DELETE FROM HotelAvailability WHERE hotel_id IN You may want to call a Java method in the trigger action to perform some set of actions. In the body of this method, you would not be able to use the OLD or NEW transition tables, so you would have to use the VTI instead.
-- TriggerActions is a class alias public static void showNewBookings(Connection conn) throws SQLException { Statement s = conn.createStatement(); Util.println("New hotel bookings:"); ResultSet rs = s.executeQuery( "SELECT * FROM new TriggerNewTransitionRows() AS EQ"); while (rs.next()) { Util.println(rs.getInt(1) + " " + rs.getDate(3) + " " + rs.getDate(4)); } rs.close(); s.close(); } The class COM.cloudscape.vti.TriggerNewTransitionRows is a VTI wrapper around a TriggerExecutionContext.getNewRowSet(). Selecting from this VTI is the equivalent of the NEW TABLE transition table. The only difference is that this VTI is accessible from within an SQL-J statement executed by a Java method called by the trigger action; the NEW transition table is accessible only by the trigger action's SQL. For examples of using the VTIs within Java methods called by the trigger action, see Programming Trigger Actions in the Cloudscape Developer's Guide. CREATE TRIGGER Examples
-- A statement and before trigger,
-- a statement and before trigger that uses special VTI
-- use a transition variable in a row trigger
-- For each row being deleted,
-- automatically update the column that records
-- perform some actions if only specific columns NOTE: You can find more examples in Programming Trigger Actions in the Cloudscape Developer's Guide. Trigger RecursionIt is possible for one trigger to cause another trigger to fire, and thus it is possible for triggers to recurse infinitely. Cloudscape supplies the property cloudscape.language.triggerMaximumRecursionLevel, which specifies the maximum recursion level, to allow you to limit trigger recursion. The default value of this property is 16. For more information, see cloudscape.language.triggerMaximumRecursionLevel in Tuning Cloudscape. Special Functions and TriggersSpecial system functions that return information about the current time or current user are evaluated when the trigger fires, not when it is created. Such functions include: Cloudscape synchronization and TriggersIn a Cloudscape synchronization triggers are not published by default. For information, see the Cloudscape Synchronization Guide. Dependency SystemThe trigger action may reference database objects other than the table upon which the trigger is declared. If any of these database objects is dropped, the trigger is invalidated. If the trigger cannot be successfully recompiled upon the next execution, the invocation throws an exception and the statement that caused it to fire will be rolled back. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |