Posted by DA Morgan on 07/21/07 04:44

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;
/
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu (replace x with u to respond)

[Back to original message]