Database detail:Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
TABLE SCRIPT: create table "employees
(employee_id number(6,0),
first_name varchar2(20 byte),
last_name varchar2(25 byte) not null enable,
email varchar2(25 byte) not null enable,
phone_number varchar2(20 byte),
hire_date date not null enable,
job_id varchar2(10 byte) not null enable,
salary number(8,2),
commission_pct number(2,2),
manager_id number(6,0),
department_id number(4,0)
);
TRIGGER CODE:create or replace TRIGGER emp_ct FOR
INSERT ON employees
COMPOUND TRIGGER
TYPE emp_t IS TABLE OF employees%rowtype INDEX BY BINARY_INTEGER;
emps emp_t;
ind SIMPLE_INTEGER := 0;
BEFORE STATEMENT IS BEGIN
NULL;
END BEFORE STATEMENT;
BEFORE EACH ROW IS BEGIN
NULL;
END BEFORE EACH ROW;
AFTER EACH ROW IS BEGIN
ind := ind + 1;
emps(ind).employee_id :=:NEW.employee_id;
emps(ind).first_name :=:NEW.first_name;
DBMS_OUTPUT.PUT_LINE('AFTER EACH ROW emps.COUNT='|| emps.COUNT);
END AFTER EACH ROW;
AFTER STATEMENT IS BEGIN
DBMS_OUTPUT.PUT_LINE('AFTER STATEMENT emps.COUNT='|| emps.COUNT);
END AFTER STATEMENT;
END;
Test CASE-1:When I insert data into EMPLOYEES using construct insert into EMPLOYEES () values () as given below why the trigger statement trigger fire for each row. I am not able to achieve bulk insert. For testing purpose, I am not actually inserting data into table just outputting.
REM INSERTING into EMPLOYEES
SET DEFINE OFF;
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (198,'Donald','OConnell','DOCONNEL','650.507.9833',to_date('21-JUN-07','DD-MON-RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (199,'Douglas','Grant','DGRANT','650.507.9844',to_date('13-JAN-08','DD-MON-RR'),'SH_CLERK',2600,null,124,50);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (200,'Jennifer','Whalen','JWHALEN','515.123.4444',to_date('17-SEP-03','DD-MON-RR'),'AD_ASST',4400,null,101,10);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (201,'Michael','Hartstein','MHARTSTE','515.123.5555',to_date('17-FEB-04','DD-MON-RR'),'MK_MAN',13000,null,100,20);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (202,'Pat','Fay','PFAY','603.123.6666',to_date('17-AUG-05','DD-MON-RR'),'MK_REP',6000,null,201,20);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (203,'Susan','Mavris','SMAVRIS','515.123.7777',to_date('07-JUN-02','DD-MON-RR'),'HR_REP',6500,null,101,40);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (204,'Hermann','Baer','HBAER','515.123.8888',to_date('07-JUN-02','DD-MON-RR'),'PR_REP',10000,null,101,70);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (205,'Shelley','Higgins','SHIGGINS','515.123.8080',to_date('07-JUN-02','DD-MON-RR'),'AC_MGR',12008,null,101,110);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (206,'William','Gietz','WGIETZ','515.123.8181',to_date('07-JUN-02','DD-MON-RR'),'AC_ACCOUNT',8300,null,205,110);
Insert into EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (100,'Steven','King','SKING','515.123.4567',to_date('17-JUN-03','DD-MON-RR'),'AD_PRES',24000,0.4,null,90);
OUTPUT: AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
AFTER EACH ROW emps.COUNT=1
AFTER STATEMENT emps.COUNT=1
....
Test CASE-2:When I perform the insert operation using construct: INSERT INTO ... SELECT
I can see for each insert, rows are accumulated in array and the statement level trigger fires only once.
I am trying to understand this behavior. Could you please explain me.
INSERT INTO employees (
employee_id, first_name, last_name, email, hire_date, job_id
)
SELECT 1000+ROWNUM, 'Emp'||ROWNUM, 'Emp'||ROWNUM, 'emp'||ROWNUM||'@abc.com', sysdate, 1
FROM dual
CONNECT BY LEVEL <= 10;
OUTPUT AFTER EACH ROW emps.COUNT=1
AFTER EACH ROW emps.COUNT=2
AFTER EACH ROW emps.COUNT=3
AFTER EACH ROW emps.COUNT=4
AFTER EACH ROW emps.COUNT=5
AFTER EACH ROW emps.COUNT=6
AFTER EACH ROW emps.COUNT=7
AFTER EACH ROW emps.COUNT=8
AFTER EACH ROW emps.COUNT=9
AFTER EACH ROW emps.COUNT=10
AFTER STATEMENT emps.COUNT=10