Posted by Serge Rielau on 07/21/07 13:36

DA Morgan wrote:
> 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.
> 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
Daniel,

Please check the newsgroup. This is a MS SQL Server group, not DB2.
Please remain stay on topic. I'll gladly answer to your DB2 questions in
the appropriate forum.

But since you have identified OLD TABLE and NEW TABLE, perhaps you can
bless us with the syntax for an Orcale statement trigger that uses them
(or their equivalents, let's not get hung up on syntax after all)
Perhaps I just can't find :old_table and :new_table in the books.

To the best of my knowledge there is no such thing as OLD TABLE/INSERTED
and NEW TABLE/DELETED in Oracle, hence there are no statement triggers
in Oracle which actually can operate on the data set.
And that was Alex's point.

If an Oracle statement trigger can access OLD ROW and NEW ROW and
obviously fire only once than I do not have a better word than "running
into a row" because by definition that row would be randomly selected
from the intermediate resultset for e.g.
INSERT INTO T SELECT * FROM S.
SQL being set oriented and all...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[Back to original message]