db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: Trigger questions
Date Tue, 24 Dec 2013 17:31:19 GMT


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 <tdudgeon-jNDFPZUTrfSltj2lJ5oNTVjMPmZJtkid@public.gmane.org> 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,
>



Mime
View raw message