When the REPLACE statement and INSERT … ON DUPLICATE KEY UPDATE statements are executed, the trigger is executed in CUBRID, while DELETE, UPDATE, INSERT jobs occur internally. The following table shows the order in which the trigger is executed in CUBRID depending on the event that occurred when the REPLACE or INSERT … ON DUPLICATE KEY UPDATE statement is executed. Both the REPLACE statement and INSERT … ON DUPLICATE KEY UPDATE statement do not execute triggers in the inherited class (table).
Execution Sequence of Triggers in the REPLACE and the INSERT … ON DUPLICATE KEY UPDATE statements
Event |
Execution Sequence of Triggers |
---|---|
REPLACE |
BEFORE DELETE > |
INSERT … ON DUPLICATE KEY UPDATE |
BEFORE UPDATE > |
REPLACE, INSERT … ON DUPLCATE KEY UPDATE |
BEFORE INSERT > |
The folllowing is an example in which the trigger inserts records to the trigger table if INSERT … ON DUPLICATE KEY UPDATE and RELPACE are executed in the with_trigger table.
CREATE TABLE with_trigger (id INT UNIQUE);
INSERT INTO with_trigger VALUES (11);
CREATE TABLE trigger_actions (val INT);
CREATE TRIGGER trig_1 BEFORE INSERT ON with_trigger EXECUTE INSERT INTO trigger_actions VALUES (1);
CREATE TRIGGER trig_2 BEFORE UPDATE ON with_trigger EXECUTE INSERT INTO trigger_actions VALUES (2);
CREATE TRIGGER trig_3 BEFORE DELETE ON with_trigger EXECUTE INSERT INTO trigger_actions VALUES (3);
INSERT INTO with_trigger VALUES (11) ON DUPLICATE KEY UPDATE id=22;
SELECT * FROM trigger_actions;
va
==============
2
REPLACE INTO with_trigger VALUES (22);
SELECT * FROM trigger_actions;
va
==============
2
3
1