foo table
| row# | foo_col1 | foo_col2 | foo_col3 |
| -----|----------|----------|----------|
| 0 | 1 | 5 | 1 |
| 1 | 2 | 3 | 3 |
| 2 | 3 | 2 | 4 |
foo_1 table
| row# | foo1_col1 | foo1_col2 | foo1_col3 | foo1_col4 |
| -----|-----------|-----------|-----------|-----------|
| 0 | 1 | 1 | NULL | 3 |
| 1 | 2 | 1 | 3 | 3 |
| 2 | 3 | 1 | NULL | 1 |
foo1_col1 are foreign keys, referring to the foo_col1
I have 3 triggers similar to these:
CREATE TRIGGER trg1 AFTER UPDATE OF foo1_col2 ON foo_1 WHEN NEW.foo1_col3 IS NOT NULL
BEGIN
UPDATE foo SET foo_col2 = foo_col2 + 1 WHERE foo.foo_col1 = NEW.foo1_col1;
/* UPDATE statement 2 */
/* UPDATE statement 3 */
END
CREATE TRIGGER trg2 AFTER UPDATE OF foo1_col2 ON foo_1 WHEN NEW.foo1_col4 != 1
BEGIN
/* UPDATE statement */
END
CREATE TRIGGER trg3 AFTER UPDATE OF foo_col2 ON foo WHEN NEW.foo_col3 >= 1
BEGIN
/* UPDATE statement */
END
trg1 and trg2 triggers are both "listening" on foo1_col2 but have different situation where it needed to be execute, so I figure to create two triggers instead of one.
Now what I want is to insert some or all row data from foo table into a temporary table but only when foo1_col2 is updated and all the statements in 3 triggers are executed and returned, because I need the updated data.
My current solution is to create another TRIGGER that will execute when foo1_col2 is updated then insert the corresponding row data from foo table using the NEW.foo1_col1 but I'm not sure it will execute only after the 3 triggers are executed and returned.
e.g.
if row# 1 foo1_col2 from foo_1 table is updated, I want row# 1 from foo table to be inserted in a temporary table but only after the 3 triggers are executed and returned.