db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Trigger questions
Date Mon, 23 Dec 2013 12:08:16 GMT
Tim Dudgeon <tdudgeon@informaticsmatters.com> 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,

-- 
Knut Anders

Mime
View raw message