1
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.

Paulo
  • 11
  • 2

0 Answers0