db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Trigger questions
Date Fri, 20 Dec 2013 19:54:45 GMT
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


View raw message