Re: Triggers

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 Received on Wed Jan 19 2005 - 20:41:14 CST