Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Triggers

Re: Triggers

From: Oradba Linux <techiey2k3_at_comcast.net>
Date: Wed, 19 Jan 2005 22:19:12 -0500
Message-ID: <Id2dnec-MOaevnLcRVn-gg@comcast.com>

<fitzjarrell_at_cox.net> wrote in message
news:1106188874.134777.319330_at_f14g2000cwb.googlegroups.com...
>
> Oradba Linux wrote:
> > "Oradba Linux" <techiey2k3_at_comcast.net> wrote in message
> > news:Y7idnVuXLe00lXLcRVn-iw_at_comcast.com...
> > > On the hr.employees table, i have a 3 triggers - before update ,
> before
> > > update for each row , after update for each row ..
> > > They only have dbms_output as part of the code.
> > >
> > > SQL> create or replace trigger trg_employees_bu
> > > 2 before update on employees
> > > 3 begin
> > > 4 dbms_output.put_line('Firing Before update .....');
> > > 5 end;
> > > 6 /
> > >
> > > Trigger created.
> > >
> > > SQL> select trigger_name from user_triggers;
> > >
> > > TRIGGER_NAME
> > > ------------------------------
> > > TRG_EMPLOYEES_AUFER
> > > TRG_EMPLOYEES_BU
> > > TRG_EMPLOYEES_BUFER
> > >
> > >
> > > SQL> update employees set salary=salary where employee_id=195;
> > > Firing Before update .....
> > > Firing Trigger Before Update for each row.....
> > > Firing trigger after update for each row .....
> > >
> > > 1 row updated.
> > >
> > > SQL> roll;
> > > Rollback complete.
> > >
> > > I understand the sequence here. First fires the trigger stmt level
> and row
> > > level triggers.
> > >
> > > SQL> update employees set salary=salary*.1 where employee_id=195;
> > > update employees set salary=salary*.1 where employee_id=195
> > > *
> > > ERROR at line 1:
> > > ORA-02290: check constraint (TEST_PLSQL.CK_SALARY) violated
> > >
> > >
> > > SQL> update employees set salary=salary where employee_id=195;
> > > Firing Before update .....
> > > Firing Trigger Before Update for each row.....
> > > Firing trigger after update for each row .....
> > > Firing Before update .....
> > > Firing Trigger Before Update for each row.....
> > > Firing trigger after update for each row .....
> > >
> > > 1 row updated.
> > >
> > > When an update fails, then why i am seeing the trigger firing twice
> even
> > > though it updated only one row.
> > >
> > >
> > >
> > >
> > >
> >
> > SQL> update employees set salary=salary-2000 where employee_id in
> (194,195);
> > update employees set salary=salary-2000 where employee_id in
> (194,195)
> > *
> > ERROR at line 1:
> > ORA-02290: check constraint (TEST_PLSQL.CK_SALARY) violated
> >
> >
> > SQL> exec null;
> > Firing Before update .....
> > Firing Trigger Before Update for each row.....
> > Firing trigger after update for each row .....
> > Firing Trigger Before Update for each row.....
> > Firing trigger after update for each row .....
> >
> > PL/SQL procedure successfully completed.
> >
> > Those are from the previous dbms_output commands.
>
> The trigger is not firing twice in the successful update. The
> dbms_output buffer contains output from both the failed update and the
> succesful update, making it appear you've fired it twice in a single
> transaction. The output cannot be rolled back, hence it remains in the
> buffer until it's flushed to the terminal.
>
> David Fitzjarrell
>

all the triggers fire even after the check constraint fails. Received on Wed Jan 19 2005 - 21:19:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US