Trigger questions

[Tim Dudgeon <td...@informaticsmatters.com>]

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

Re: Trigger questions

[Tim Dudgeon <td...@informaticsmatters.com>]

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 <td...@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,
>

Re: Trigger questions

[Knut Anders Hatlen <kn...@oracle.com>]

Tim Dudgeon <td...@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