The INSERT | UPDATE | DELETE clause 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 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 may be some performance impact if you try to load the data with triggers enabled. Depending on your business need, you may 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 using the UPDATE clause:
▪Within the REFERENCES clause of a trigger, a column may be referenced (aliased) no more than once in the BEFORE sub-clause and once in the AFTER sub-clause. Also, if the column is referenced in both the BEFORE and AFTER sub-clauses, the column's 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.