|
|
Posted by Serge Rielau on 07/21/07 05:25
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
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Navigation:
[Reply to this message]
|