SQL Statements › CREATE TRIGGER/RULE

CREATE TRIGGER/RULE

For an overview and examples of procedures and triggers, see Procedures and Triggers.

In the following table, YES indicates a valid execution method for this statement.

The CREATE TRIGGER/RULE statement creates a user-specified set of instructions whose execution is triggered by a specified maintenance request against a certain base table. See Transaction Integrity for information about when a created trigger is recognized by applications that are currently executing.

Note the following:

• Statement-level rules are not supported by CA Datacom/DB, that is, a TRIGGER/RULE can only be invoked for each row affected, not once for a statement that can affect many rows.
• Use of host variables, and therefore OUT and INOUT parameters, is forbidden in a procedure call that is part of a triggered action.
• To allow the trigger to be recognized during the execution of the plan, plans containing SQL statements that reference the table to which the trigger is being added are marked invalid and automatically rebound the next time they are executed.
• A single event can invoke multiple triggers. When this occurs, the triggers are invoked according to the date and time created with the most recently created being invoked last.

With regard to table partitioning, CREATE statements may not be issued against a table which is partitioned nor against a partition. For more information about table partitioning, see the CA Datacom/DB Database and System Administration Guide.

►►─ CREATE ─┬─ TRIGGER ─┬─ trig-name ─┬─ BEFORE ─┬────────────────────────────►
            └─ RULE ────┘             └─ AFTER ──┘

 ►─ trig-event ─┬─ ON ───┬─ table-name ─┬───────────────────┬─ trig-action ───►◄
                ├─ OF ───┤              └─ trig-references ─┘
                ├─ FROM ─┤
                └─ INTO ─┘
   ┌─ , ───────────────────────────────────┐
►►─▼─┬─ INSERT ──────────────────────────┬─┴──────────────────────────────────►◄
     ├─ UPDATE ─┬──────────────────────┬─┤
     │          └─ OF ─ (column-list) ─┘ │
     └─ DELETE ──────────────────────────┘
►►─ REFERENCING ─┬─ old-row ─────────┬────────────────────────────────────────►◄
                 ├─ new-row ─────────┤
                 ├─ old-row,new-row ─┤
                 └─ new-row,old-row ─┘
►►─ OLD ─┬───────┬─ AS ─ correlation-name ────────────────────────────────────►◄
         └─ ROW ─┘
►►─ NEW ─┬───────┬─ AS ─ correlation-name ────────────────────────────────────►◄
         └─ ROW ─┘
►►─┬────────────────┬─┬───────────────┬─┬─ call-procedure ────┬───────────────►◄
   └─ FOR EACH ROW ─┘ └─ when-clause ─┘ └─ execute-procedure ─┘
►►─┬─ WHEN ──┬─ boolean-expression ───────────────────────────────────────────►◄
   └─ WHERE ─┘

CREATE TRIGGER/RULE

(Required) Specify for the CREATE either a TRIGGER or a RULE.

trig-name

Specify the SQL-name of the trigger. SQL-names are SQL-identifiers.

BEFORE/AFTER

Trigger BEFORE or AFTER the CA Datacom maintenance occurs.

trig-event ON/OF/FROM/INTO table-name

Specify a trigger event, one of the listed options, and a table name. Options ON, OF, FROM, and INTO are supplied for syntax compatibility.

INSERT/UPDATE/DELETE

Part of the trig-event syntax. You can use up to three comma-separated occurrences of these three maintenance types (INSERT, UPDATE, DELETE), but each maintenance type can only be used once in the trig-event definition.

OF

(Optional) Part of the trig-event syntax. Supplied for syntax compatibility.

(column-list)

is a list of columns whose value-change triggers a trigger. Omission implies all columns are triggers.

REFERENCING

Part of the trig-references syntax.

OLD

Part of the old-row syntax. Refers to the row prior to UPDATE or DELETE.

NEW

Part of the new-row syntax. Refers to the row that results from execution of the INSERT or UPDATE.

ROW

(Optional) Part of the old-row and new-row syntax.

AS correlation-name

Part of the old-row and new-row syntax. The correlation-name is the name which is to be used to reference a column from the before-maintenance or after-maintenance image of the row being maintained. For example, if ACCOUNT_NUMBER is a column name, OLD_ACCOUNT_ROW is the before-image correlation-name, and NEW_ACCOUNT_ROW, then OLD_ACCOUNT_ROW.ACCOUNT_NUMBER can be used to refer to the account number prior to an UPDATE (or DELETE), and NEW_ACCOUNT_ROW.ACCOUNT_NUMBER may be used to refer to the account number after an UPDATE (or INSERT) occurs. The use of this name is, of course, limited to the CREATE statement itself. Also see Correlation Names and SQL Index Binding.

FOR EACH ROW

(Optional) Part of the trig-action syntax. Denotes this trigger as one that executes once per maintained row rather than once per SQL statement. ROW level is used (the default) even if you do not specify this optional clause.

call-procedure

Part of the trig-action syntax.

As a convenience to users of CREATE TRIGGER who need to pass large base table rows to a triggered procedure, a special DATACOM_WHOLE_ROW column is available. DATACOM_WHOLE_ROW is only visible during execution of the CREATE TRIGGER statement. When you pass the special DATACOM_WHOLE_ROW column name to your procedure, the procedure receives a CHAR parameter containing the entire row in CA Datacom/DB format. Following is an example of the use of DATACOM_WHOLE_ROW:

 CREATE TRIGGER myTrigger AFTER DELETE FROM myTable
   REFERENCING OLD ROW AS old_row
   CALL handleMaintenance('DELETE', old_row.datacom_whole_row)

We recommend that you use this special DATACOM_WHOLE_ROW column name only if your parameter list becomes unwieldy because of an excessive number of columns being passed.

execute-procedure

Part of the trig-action syntax.

As a convenience to users of CREATE TRIGGER who need to pass large base table rows to a triggered procedure, a special DATACOM_WHOLE_ROW column is available. DATACOM_WHOLE_ROW is only visible during execution of the CREATE TRIGGER statement. See the information previously given in the call-procedure description about DATACOM_WHOLE_ROW.

WHEN/WHERE boolean-expression

This is the when-clause syntax. The when-clause executes before the triggered action (the procedure call) is taken and cancels execution of the procedure if the boolean-expression evaluates to UNKNOWN or FALSE. The boolean-expression is similar to the WHERE clause of the SELECT statement but may not include subqueries or functions of any type. The correlation-name (see previous description) can be used to reference values in the row being maintained.

The trigger only activates on rows for which the boolean-expression evaluates to TRUE. If the WHEN clause containing the boolean-expression is omitted, the trigger always triggers.