Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Statement level trigger...

sgalaxyDec 1 2007 — edited Dec 1 2007

Hi ,
I know that the following code regarding the after statement level trigger has not practical use.... but i did it to examine what the Oracle doc points out about the statement - level triggers.....

A statement trigger is fired once on behalf of the triggering statement, 
regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected.

So .....

create or replace trigger trg_emp_
after insert on emp

begin
  insert into emp_(empno,sal) select empno,sal from emp;
end;
/

insert into emp(empno,sal) values(6231,789)
/
commit;
insert into emp(empno,sal) values(1080,900)
/
commit;
The rows in emp after the insert:
SQL> select empno,sal from emp order by empno;

EMPNO       SAL
----- ---------
 1000    900,00
 1010    900,00
 1080    900,00
 4567   3002,00
 6231    789,00
 6981    789,00
 6984    789,00
 7369    500,00
 7499    500,00
 7521    800,00
 7566   1000,00
 7654   1400,00
 7698   2100,10
 7782   2300,10
 7788    790,00
 7839   1430,00
 7844    280,00
 7876    650,00
 7890    700,00
 7900    570,00

EMPNO       SAL
----- ---------
 7902    500,00
 7934    140,10
The rows in emp_ after the insert:
SQL> select empno,sal from emp_ order by empno;

EMPNO       SAL
----- ---------
 1000    900,00
 1000    900,00
 1010    900,00
 1010    900,00
 4567   3002,00
 4567   3002,00
 6231    789,00
 6981    789,00
 6981    789,00
 6984    789,00
 6984    789,00
 7369    500,00
 7369    500,00
 7499    500,00
 7499    500,00
 7521    800,00
 7521    800,00
 7566   1000,00
 7566   1000,00
 7654   1400,00

EMPNO       SAL
----- ---------
 7654   1400,00
 7698   2100,10
 7698   2100,10
 7782   2300,10
 7782   2300,10
 7788    790,00
 7788    790,00
 7839   1430,00
 7839   1430,00
 7844    280,00
 7844    280,00
 7876    650,00
 7876    650,00
 7890    700,00
 7890    700,00
 7900    570,00
 7900    570,00
 7902    500,00
 7902    500,00
 7934    140,10
 7934    140,10

I only confirm the Oracle doc ... about the statement - level trigger that the record with empno=1080 is not inserted but the record with empno=6231 inserted into table emp_ because the statement - level fired once for the 'packet' of the two rows inserted in the emp table....(look at the two insert statemant above....)

I cannot understand however ... why all other records were inserted twice .....
Can somebody explain it.....????What haven't i understood...?????
(I have truncated the table emp_ before inserting into the table emp....)

Thanks....
Sim

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 29 2007
Added on Dec 1 2007
13 comments
730 views