Posted by DA Morgan on 07/21/07 08:09

Serge Rielau wrote:
> DA Morgan wrote:
>> Alex Kuznetsov wrote:
>>> On Jul 20, 5:49 pm, DA Morgan <damor...@psoug.org> wrote:
>>>> Erland Sommarskog wrote:
>>>>> (candide...@yahoo.de) writes:
>>>>>> I met a SS-Professional yesterday and he told me to use stored
>>>>>> procedures. As there was no time to waste I did so and it seems to
>>>>>> work.
>>>>>> Still wondering there's no Before-Trigger event in SS2005. maybe in
>>>>>> SS2008?
>>>>> I have not heard anything on that.
>>>>> I fond a request for BEFORE TRIGGERS on
>>>>> https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?Fe...
>>>>>
>>>>> that you can vote for if you like.
>>>> SQL Server is now the only major commercial database without them.
>>>>
>>>> Given how easy they would be to implement, Oracle had them in 1989,
>>>> does anyone know why the delay?
>>>> --
>>>> Daniel A. Morgan
>>>> University of Washington
>>>> damor...@x.washington.edu (replace x with u to respond)
>>>
>>> Agreed, but on the other hand Oracle fires triggers once per row, not
>>> once per statement - and that can really drag performance. No RDBMS is
>>> perfect...
>>
>> On the other hand you are incorrect. Oracle gives developers the choice
>> of firing one per row or once per statement and always has. In fact the
>> default is once per statement. But no none is perfect. If they were we
>> would all be unemployed.
>>
>> Here are some samples so you can tell the difference:
>>
>> CREATE OR REPLACE TRIGGER statement_level
>> BEFORE UPDATE
>> ON orders
>> DECLARE
>> vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
>> BEGIN
>> dbms_output.put_line(vMsg);
>> END statement_level;
>> /
>>
>> CREATE OR REPLACE TRIGGER statement_level
>> BEFORE UPDATE
>> FOR EACH ROW <---- if this isn't here it is statement level
>> ON orders
>> DECLARE
>> vMsg VARCHAR2(30) := 'Row Level Trigger Fired';
>> BEGIN
>> dbms_output.put_line(vMsg);
>> END statement_level;
>> /
> I beg to differ. A trigger that fires only for the first row it runs
> into is NOT a statement trigger.
> Statement triggers allow access to the NEW TABLE (aka INSERTED) and OLD
> TABLE (DELETED). Also a statement trigger fires even if no row is
> modified. I'm unsure that is the case in Oracle.
>
> Does Oracle still have issues with "mutating table conflicts" or is that
> finally fixed in 11g?
>
> I'd be careful with throwing stones in the glass house.
>
> Cheers
> Serge

No stones ... just correcting an incorrect statement. Can't talk about
11g ... you'll have to wait just like everybody else. Then you can try
to flatter Mark through imitation. <g>

However I am fascinated by your technical description of a statement
level trigger. How does a trigger "run into" a row? Is it more likely
to happen if its been out drinking?

And perhaps I can familiarize you with the documentation on the product
you work on for IBM.
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2.doc.sqlref/rctrg.htm

Take a good look at table 76: Allowable combinations of attributes in a
trigger definition.

OLD and NEW are only allowed for ROW level triggers in DB2 not STATEMENT
level. It appears that you are confusing NEW and OLD with NEW_TABLE AS
and OLD_TABLE AS.

Also see:

Notes:
1. The same clause must not be specified more than once. OLD TABLE
and NEW TABLE must be specified only for AFTER triggers.
2. FOR EACH STATEMENT must not be specified for BEFORE triggers.

Statement #2 conflicts with Table 76 which clearly shows FOR EACH
STATEMENT and BEFORE.

But heck you finally built Instead Of Triggers into 9.1 for z/OS. That's
progress <g>
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

[Back to original message]