The CREATE TRIGGER statement is used to create the trigger the clause specifies the table name
The CREATE TRIGGER statement defines a trigger in the database. Triggers can be created to support general forms of integrity or business rules. A trigger defines a set of actions that are executed with, or triggered by, an INSERT, UPDATE, or DELETE statement. Show InvocationThis statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509). AuthorizationThe privileges held by the authorization ID of the statement must include at least one of the following authorities:
If the authorization ID of the statement does not have DATAACCESS authority, the privileges (excluding group privileges) held by the authorization ID of the statement must include all of the following authorities, as long as the trigger exists:
Group privileges are not considered for any table or view specified in the CREATE TRIGGER statement. To replace an existing trigger, the authorization ID of the statement must be the owner of the existing trigger (SQLSTATE 42501). If the SECURED option is specified, the privileges held by the authorization ID of the statement must additionally include SECADM or CREATE_SECURE_OBJECT authority (SQLSTATE 42501). SyntaxCREATEOR REPLACETRIGGERtrigger-nameNO CASCADEBEFOREAFTERINSTEAD OFtrigger-eventONtable-nameview-nameREFERENCING12OLDAScorrelation-nameNEWAScorrelation-nameOLD TABLEASidentifierNEW TABLEASidentifierFOR EACH ROW3FOR EACH STATEMENTNOT SECUREDSECUREDtriggered-action ORINSERTDELETEUPDATEOF,column-name4 5WHEN(search-condition)label:SQL-procedure-statement CALLCompound SQL (compiled)6Compound SQL (inlined)FORWITH,common-table-expressionfullselectGET DIAGNOSTICSIFINSERTITERATELEAVEMERGEsearched-deletesearched-updateSET VariableSIGNALWHILE Notes:
DescriptionOR REPLACESpecifies to replace the definition for the trigger if one exists at the current server. The existing definition is effectively dropped before the new definition is replaced in the catalog. This option is ignored if a definition for the trigger does not exist at the current server. This option can be specified only by the owner of the object.trigger-nameNames the trigger. The name, including the implicit or explicit schema name, must not identify a trigger already described in the catalog (SQLSTATE 42710). If a two-part name is specified, the schema name cannot begin with SYS (SQLSTATE 42939).NO CASCADE BEFORESpecifies that the associated triggered action is to be applied before any changes caused by the actual update of the subject table are applied to the database. It also specifies that the triggered action of the trigger will not cause other triggers to be activated.AFTERSpecifies that the associated triggered action is to be applied after the changes caused by the actual update of the subject table are applied to the database.INSTEAD OFSpecifies that the associated triggered action replaces the action against the subject view. Only one INSTEAD OF trigger is allowed for each kind of operation on a given subject view (SQLSTATE 428FP).trigger-eventSpecifies that the triggered action associated with the trigger is to be executed whenever one of the events is applied to the subject table or subject view. Any combination of the events can be specified, but each event (INSERT, DELETE, and UPDATE) can only be specified once (SQLSTATE.42613). If multiple events are specified, the triggered action must be a compound SQL (compiled) statement (SQLSTATE 42601).INSERTSpecifies that the triggered action associated with the trigger is to be executed whenever an INSERT operation is applied to the subject table or subject view.DELETESpecifies that the triggered action associated with the trigger is to be executed whenever a DELETE operation is applied to the subject table or subject view.UPDATESpecifies that the triggered action associated with the trigger is to be executed whenever an UPDATE operation is applied to the subject table or subject view, subject to the columns specified or implied.If the optional column-name list is not specified, every column of the table or view is implied. Therefore, omission of the column-name list implies that the trigger will be activated by the update of any column of the table or view.OF column-name,...Each column-name specified must be a column of the base table (SQLSTATE 42703). If the trigger is a BEFORE trigger, the column-name specified cannot be a generated column other than the identity column (SQLSTATE 42989). No column-name can appear more than once in the column-name list (SQLSTATE 42711). The trigger will only be activated by the update of a column that is identified in the column-name list. This clause cannot be specified for an INSTEAD OF trigger (SQLSTATE 42613). ONtable-nameDesignates the subject table of the BEFORE trigger or AFTER trigger definition. The name must specify a base table or an alias that resolves to a base table (SQLSTATE 42704 or 42809). The name must not specify a catalog table (SQLSTATE 42832), a materialized query table (SQLSTATE 42997), a created temporary table, a declared temporary table (SQLSTATE 42995), or a nickname (SQLSTATE 42809).view-nameDesignates the subject view of the INSTEAD OF trigger definition. The name must specify an untyped view or an alias that resolves to an untyped view with no columns of type XML (SQLSTATE 42704 or 42809). The name must not specify a catalog view (SQLSTATE 42832). The name must not specify a view that is defined using WITH CHECK OPTION (a symmetric view), or a view on which a symmetric view has been defined, directly or indirectly (SQLSTATE 428FQ).NOT SECURED or SECUREDSpecifies whether the trigger is considered secure. The default is NOT SECURED.NOT SECUREDSpecifies the trigger is considered not secure.SECUREDSpecifies the trigger is considered secure. SECURED must be specified for a trigger whose subject table is a table on which row level or column level access control has been activated (SQLSTATE 428H8). Similarly, SECURED must be specified for a trigger that is created on a view and one or more of the underlying tables in that view definition has row level or column level access control activated (SQLSTATE 428H8).REFERENCINGSpecifies the correlation names for the transition variables and the table names for the transition tables. Correlation names identify a specific row in the set of rows affected by the triggering SQL operation. Table names identify the complete set of affected rows. Each row affected by the triggering SQL operation is available to the triggered action by qualifying columns with correlation-names specified as follows.OLD AS correlation-nameSpecifies a correlation name which identifies the row state before the triggering SQL operation.NEW AS correlation-nameSpecifies a correlation name which identifies the row state as modified by the triggering SQL operation and by any SET statement in a BEFORE trigger that has already executed.The complete set of rows affected by the triggering SQL operation is available to the triggered action by using a temporary table name specified as follows. OLD TABLE AS identifierSpecifies the name of a temporary table that identifies the values in the complete set of affected rows prior to the triggering SQL operation. If the trigger event is INSERT, the temporary table is empty.NEW TABLE AS identifierSpecifies the name of a temporary table that identifies the state of the complete set of affected rows as modified by the triggering SQL operation and by any SET statement in a before trigger that has already been executed. If the trigger event is DELETE, the temporary table is empty.The following rules apply to the REFERENCING clause:
Trigger event predicates can be used anywhere in the triggered action of a CREATE TRIGGER statement that uses a compound SQL (compiled) statement as the SQL-procedure-statement. The WHEN clause cannot be specified for INSTEAD OF triggers (SQLSTATE 42613). A reference to a transition variable with an XML data type can be used only in a VALIDATED predicate. label:Specifies the label for an SQL procedure statement. The label must be unique within a list of SQL procedure statements, including any compound statements nested within the list. Note that compound statements that are not nested can use the same label. A list of SQL procedure statements is possible in a number of SQL control statements.Only the FOR statement, WHILE statement, and the compound SQL statement can include a label. SQL-procedure-statementSpecifies the SQL statement that is to be part of the triggered action. A searched update, searched delete, insert, or merge operation on nicknames inside compound SQL is not supported.The triggered action of a BEFORE trigger on a column of type XML can invoke the XMLVALIDATE function through a SET statement, leave values of type XML unchanged, or assign them to NULL using a SET statement. The SQL-procedure-statement must not contain a statement that is not supported (SQLSTATE 42987). The SQL-procedure-statement cannot reference an undefined transition variable (SQLSTATE 42703), a federated object (SQLSTATE 42997), or a declared temporary table (SQLSTATE 42995). or the start and end columns of the BUSINESS_TIME period (SQLSTATE 42808). The SQL-procedure-statement in a BEFORE trigger cannot:
Notes
Examples
|