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 21:10:15 -0500
Message-ID: <Z6SdnYzoi8FHj3LcRVn-hQ@comcast.com>

"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. Received on Wed Jan 19 2005 - 20:10:15 CST

Original text of this message

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