DBA or owner of the table on which the trigger is being defined
Usage
Use the CREATE TRIGGER statement to create a trigger that executes a series of SQL statements when a particular action (an INSERT, UPDATE, or DELETE) occurs.
For more information about triggers, see Triggers.
Parameters, clauses, keywords, and variables
▪ trigger‑name: Identifies the trigger. The name can contain up to 254 characters.
▪ time‑of‑operation: Specifies whether to execute the trigger before or after the invoking DML statement. In some circumstances, the BEFORE and AFTER clauses are interchangeable. However, there are some situations where one clause is preferred over the other.
It is more efficient to use the BEFORE clause when performing data validation, such as domain constraint and referential integrity checking.
When you use the AFTER clause, table rows which become available due to the invoking DML statement are processed. Conversely, the AFTER clause also confirms data deletion after the invoking DELETE statement.
You can define up to six triggers per table, one for each combination of action (INSERT, UPDATE, DELETE) and time (BEFORE and AFTER):
– BEFORE INSERT
– BEFORE UPDATE
– BEFORE DELETE
– AFTER INSERT
– AFTER UPDATE
– AFTER DELETE
The following example shows trigger trig01 defined BEFORE INSERT ON table1.
"CREATE TRIGGER TRIG01 ON table1 BEFORE INSERT REFERENCING NEW COL1 AS NEW_COL1 BEGIN EXEC SQL PREPARE CUR1 INSERT INTO T2 VALUES (?); EXEC SQL EXECUTE CUR1 USING (NEW_COL1); EXEC SQL CLOSE CUR1; EXEC SQL DROP CUR1; END"
Following are examples (including implications and advantages) of using the BEFORE and AFTER clause of the CREATE TRIGGER statement for each DML operation:
▪ UPDATE operation
The BEFORE clause can verify that modified data follows integrity constraint rules before processing the UPDATE. If the REFERENCING NEW AS clause is used with the BEFORE UPDATE clause, then the updated values are available to the triggered SQL statements. In the trigger, you can set the default column values or derived column values before performing an UPDATE.
The AFTER clause can perform operations on newly modified data. For example, after a branch address update, the sales for the branch can be computed.
If the REFERENCING OLD AS clause is used with the AFTER UPDATE clause, then the values that existed prior to the invoking update are accessible to the triggered SQL statements.
▪ INSERT operation
The BEFORE clause can verify that new data follows integrity constraint rules before performing an INSERT. Column values passed as parameters are visible to the triggered SQL statements but the inserted rows are not. In the trigger, you can set default column values or derived column values before performing an INSERT.
The AFTER clause can perform operations on newly inserted data. For example, after insertion of a sales order, the total order can be computed to see if a customer is eligible for a discount.
Column values are passed as parameters and inserted rows are visible to the triggered SQL statements.
▪ DELETE operation
The BEFORE clause can perform operations on data about to be deleted. Column values passed as parameters and inserted rows that are about to be deleted are visible to the triggered SQL statements.
The AFTER clause can be used to confirm the deletion of data. Column values passed as parameters are visible to the triggered SQL statements. The deleted rows are visible to the triggering SQL statement.
▪ triggering‑event: Indicates the trigger action when a user action (INSERT, UPDATE, DELETE) is attempted.
Statements related to processing a trigger occur first before commits and autocommits from the invoking DML (INSERT, UPDATE, DELETE) statements on tables. If a trigger body or a procedure that is called within the trigger body attempts to execute a COMMIT or ROLLBACK, a solidDB server returns an appropriate run-time error.
– INSERT specifies that the trigger is activated by an INSERT on the table. Loading n rows of data is considered as n inserts.
Note There might be some performance impact if you try to load the data with triggers enabled. Depending on your business need, you might want to disable the triggers before loading and enable them after loading. For details, see ALTER TRIGGER.
– DELETE specifies that the trigger is activated by a DELETE on the table.
– UPDATE specifies that the trigger is activated by an UPDATE on the table. The following rules apply when you use the UPDATE clause:
▪ Within the REFERENCES clause of a trigger, a column can be referenced (aliased) no more than one time in the BEFORE sub-clause and one time in the AFTER sub-clause. Also, if the column is referenced in both the BEFORE and AFTER sub-clauses, the column alias must be different in each sub-clause.
▪ The solidDB server allows for recursive update to the same table and does not prohibit recursive updates to the same row.
The solidDB server does not detect situations where the actions of different triggers cause the same data to be updated. For example, assume there are two update triggers (one that is a BEFORE trigger and one that is an AFTER trigger) on different columns, Col1 and Col2, of table Table1. When an update is attempted on all the columns of Table1, the two triggers are activated. Both triggers call stored procedures which update the same column, Col3, of a second table, Table2. The first trigger updates Table2.Col3 to 10 and the second trigger updates Table2.Col3 to 20.
Likewise, a solidDB server does not detect situations where the result of an UPDATE which activates a trigger conflicts with the actions of the trigger itself. For example, consider the following SQL statement:
UPDATE t1 SET c1 = 20 WHERE c3 = 10;
If the trigger is activated by this UPDATE then calls a procedure that contains the following SQL statement, the procedure overwrites the result of the UPDATE that activated the trigger:
UPDATE t1 SET c1 = 17 WHERE c1 = 20;
Note The above example can lead to recursive trigger execution, which you should try to avoid.
▪ table‑name: Name of the table on which the trigger is created. solidDB server allows you to drop a table that has dependent triggers defined on it. When you drop a table all dependent objects including triggers are dropped. However, you might still get runtime errors. For example, assume you create two tables A and B. If a procedure SP-B inserts data into table A, and table A is then dropped, a user will receive a run-time error if table B has a trigger which invokes SP-B.
▪ trigger‑body: Contains the statement(s) to be executed when a trigger is initiated. The definition is identical to the stored procedure definition. For information about creating a stored procedure body, see CREATE PROCEDURE.
Note You can create a trigger with an empty body, although it is not useful.
A trigger body can also invoke any procedure registered with a solidDB server. solidDB procedure invocation rules follow standard procedure invocation practices.
You must explicitly check for business logic errors and raise an error.
▪ col‑ref: Allows you to reference the values of columns both before and after an UPDATE operation. It produces a set of old and new column values which can be passed to a stored procedure; when passed, the procedure contains logic (for example, domain constraint checking) used to determine these parameter values.
Use the OLD AS clause to alias the old identifier for the table as it exists before the UPDATE. Use the NEW AS clause to alias the new identifier for the table as it exists after the UPDATE.
If you reference both the old and new values of the same column, you must use different values for col‑id.
Each column that is referenced as NEW or OLD should have a separate REFERENCING subclause.
The statement atomicity in a trigger is such that operations made in a trigger are visible to the subsequent SQL statements inside the trigger. For example, if you execute an INSERT statement in a trigger and then also perform a select in the same trigger, then the inserted row is visible.
In the case of AFTER trigger, an inserted row or an updated row is visible in the AFTER insert trigger, but a deleted row cannot be seen for a select performed within the trigger. In the case of a BEFORE trigger, an inserted or updated row is invisible within the trigger and a deleted row is visible. In the case of an UPDATE, the pre-update values are available in a BEFORE trigger.
The following table summarizes the statement atomicity in a trigger, indicating whether the row is visible to the SELECT statement in the trigger body.
Operation
BEFORE TRIGGER
AFTER TRIGGER
INSERT
row is invisible
row is visible
UPDATE
previous value is visible
new value is visible
DELETE
row is visible
row is invisible
Triggers usage notes and restrictions
▪ To use the stored procedure that a trigger calls, provide the catalog, schema/owner and name of the table on which the trigger is defined and specify whether to enable or disable the triggers on the table.
▪ You can define, by default, up to one trigger for each combination of table, action (INSERT, UPDATE, DELETE) and time (BEFORE and AFTER). This means there can be a maximum of six triggers per table.
Note The triggers are applied to each row. This means that if there are ten inserts, a trigger is executed ten times.
▪ You cannot define triggers on a view (even if the view is based on a single table).
▪ You cannot alter a table that has a trigger defined on it when the dependent columns are affected.
▪ You cannot create a trigger on a system table.
▪ You cannot execute triggers that reference dropped or altered objects. To prevent this error:
– recreate any referenced object that you drop,
– restore any referenced object you changed back to its original state (known by the trigger).
▪ You can use reserved words in trigger statements if they are enclosed in double quotation marks. For example, the following CREATE TRIGGER statement references a column named "data" which is a reserved word.
"CREATE TRIGGER TRIG1 ON TMPT BEFORE INSERT REFERENCING NEW "DATA" AS NEW_DATA BEGIN END"
Example
"CREATE TRIGGER TRIGGER_BI ON TRIGGER_TEST BEFORE INSERT REFERENCING NEW BI AS NEW_BI BEGIN EXEC SQL PREPARE BI INSERT INTO TRIGGER_OUTPUT VALUES ( 'BI', TRIG_NAME(0), TRIG_SCHEMA(0)); EXEC SQL EXECUTE BI; SET NEW_BI = 'TRIGGER_BI'; END";