Re: Triggers

From: <fitzjarrell_at_cox.net>
Date: 19 Jan 2005 22:12:47 -0800
Message-ID: <1106201567.617995.129130@z14g2000cwz.googlegroups.com>

Oradba Linux wrote:
> <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.

No. The triggers fire at the start of and the end of the event; the check constraint only affects the data and the success or failure of the update to be applied. The dbms_output buffer is populated by the event triggers. Since the transaction never completes the buffer is never displayed, and thus it's never flushed. The contents from the failed update remain, and are appended to by the next, successful update, which displays the contents and flushes the buffer. Had you tried to output sufficient text to overflow the output buffer the second update would fail due to that buffer overflow:

SQL> create table trigtest
2 chktest number check(chktest>0),
3 chktext varchar2(20))
4 /

Table created.

SQL> create trigger test1
2 before update on trigtest
3 begin
4 dbms_output.put_line('This is a test of trigger firing. This statement-level trigger has fired.');
5 end;
6 /

Trigger created.

SQL> create trigger test2
2 before update on trigtest
3 for each row
4 begin
5 dbms_output.put_line('This is a test of trigger firing. This row-level trigger has fired.');
6 end;
7 /

Trigger created.

SQL> create trigger test3
2 after update on trigtest
3 for each row
4 begin
5 dbms_output.put_line('This is a test of trigger firing. This after update trigger has fired.');
6 end;
7 /

Trigger created.

SQL> set serveroutput on size 2000

SQL> After inserting some values, I ran an update guaranteed to fail several times, to fill the output buffer:

SQL> update trigtest set chktest = 0 where chktest = 3; update trigtest set chktest = 0 where chktest = 3 *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C007144) violated

SQL> /
update trigtest set chktest = 0 where chktest = 3 *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C007144) violated

SQL> /
update trigtest set chktest = 0 where chktest = 3 *
ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 198
ORA-06512: at "SYS.DBMS_OUTPUT", line 139
ORA-06512: at "SCOTT.TEST1", line 2
ORA-04088: error during execution of trigger 'SCOTT.TEST1'

Note the check constraint did not cause the update to fail; the allocated output buffer (2000 bytes) was filled to overflowing by the trigger output.

Clearing the buffer and starting again:

SQL> update trigtest set chktest = 0 where chktest = 4; update trigtest set chktest = 0 where chktest = 4 *
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C007144) violated

SQL> c/0/3
1* update trigtest set chktest = 3 where chktest = 4 SQL> /
This is a test of trigger firing. This statement-level trigger has fired.

This is a test of trigger firing.  This row-level trigger has fired.
This is a test of trigger firing.  This after update trigger has fired.
This is a test of trigger firing.  This statement-level trigger has

fired.
This is a test of trigger firing. This row-level trigger has fired. This is a test of trigger firing. This after update trigger has fired.

1 row updated.

SQL> Clearly the triggers are firing before the check constraint invalidates the data for the update. Had you written a statement-level after update trigger it would NOT have fired due to the check constraint error. It is the fact you have a row-level after update trigger that has lead you the erroneous conclusion the triggers are firing after the check constraint has caused the update to fail.

David Fitzjarrell Received on Thu Jan 20 2005 - 00:12:47 CST