I'd like to check that my understanding of how triggers work is correct.
1. There is no procedural language like PL/SQL. The only solution here
is to use Java stored procedures.
2. Triggers can only execute a single statement. If you need to do
multiple things then you need to create multiple triggers (and in the
right order or execution).
3. Relating to this I'm finding something I didn't expect.
I create 2 triggers on a table:
create trigger trg1 after insert on tab ... for each row ...
create trigger trg2 after insert on tab ... for each row ...
Then I do an insert like this:
insert into tab ( ... ) values ( ... )
and the triggers seem to fire like this:
trg1, trg2
So far so good.
Now I do in insert like this:
insert into tab ( ... ) values ( ... ), ( ... ), ( ... )
and it seems the triggers fire like this:
trg1, trg1, trg1, trg2, trg2, trg2
I was expecting:
trg1, trg2, trg1, trg2, trg1, trg2
But what I really want is:
trg1, trg2
4. There is no way to have a single trigger that handles
insert/update/delete. You need multiple (potentially identical) triggers
p.s. What I'm wanting to achieve is to have trigger(s) on a table that
when an insert/update/delete operation occurs results in updating a
different table with a summary of the information for the modified row
and related rows (e.g averaging that row and all other rows with some
common attribute).
Many thanks
Tim
