OK, thanks for that. Its a big help.
I took a quick play with statement triggers and it looks like it might
be what I need.
That said my real need is a bit more complex than I made out, so I need
to work through it a bit more. I'll be back if (when?) I need more help!
Tim
On 23/12/2013 12:08, Knut Anders Hatlen wrote:
Tim Dudgeon <[email protected]> writes:
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).
That's right. The SQL standard has syntax for multi-statement triggers,
but Derby doesn't support that syntax yet. So stored procedures or
multiple triggers are the alternatives for now.
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
I can see how that's not the expected behaviour, and it may be wrong. I
did a quick check of what the SQL standard says on this, and I *think*
Derby follows the letter of the standard here.
But what I really want is:
trg1, trg2
Sounds like you want statement-level triggers instead of row-level
triggers. That could be achieved by using FOR EACH STATEMENT instead of
FOR EACH ROW in the CREATE TRIGGER statements.
4. There is no way to have a single trigger that handles
insert/update/delete. You need multiple (potentially identical)
triggers
Correct.
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).
I think statement-level triggers should be able to do that job. It is
possible to make the before and after image of the changed rows
available to the trigger using a REFERENCING clause in the CREATE
TRIGGER statement.
For example, if you want every insert statement on MY_TABLE to be
recorded in a table called CHANGES, with information about the number of
rows inserted and the average value of the inserted rows, you could
define a trigger similar to this one:
CREATE TRIGGER tr AFTER INSERT ON my_table
REFERENCING NEW TABLE AS NEW
FOR EACH STATEMENT
INSERT INTO changes(num_changed_rows, avg_value)
SELECT COUNT(*), AVG(value) FROM NEW
Hope this helps,
