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