CREATE TABLE TAB1
(
“TAB1_KEY” NUMBER,
“NUMB1″ NUMBER,
“ACTION_DT” DATE,
“USER_ID” VARCHAR2(60 BYTE),
“ACTION” VARCHAR2(1500 BYTE)
);
CREATE TABLE TAB_HIST
(
“ACTION_DT” DATE,
“USER_ID” VARCHAR2(60 BYTE),
“ACTION” VARCHAR2(1500 BYTE)
);
– Compound Trigger
create or replace
TRIGGER TRG_TAB1_INSERT
FOR INSERT ON TAB1
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
INSERT INTO TAB_HIST(ACTION_DT,USER_ID,action) VALUES(SYSDATE,’Trigger’,'START BEFORE STATEMENT’);
– NULL; — Do something here.
INSERT INTO TAB_HIST(ACTION_DT,USER_ID,action) VALUES(SYSDATE,’Trigger’,'END BEFORE STATEMENT’);
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
INSERT into TAB_HIST(ACTION_DT,USER_ID,action) values(sysdate,’Trigger’,'START BEFORE EACH ROW’);
–
INSERT into TAB_HIST(ACTION_DT,USER_ID,action) values(sysdate,’Trigger’,'END BEFORE EACH ROW’);
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
INSERT into TAB_HIST(ACTION_DT,USER_ID,action) values(sysdate,’Trigger’,'START AFTER EACH ROW’);
INSERT INTO TAB_HIST(ACTION_DT,USER_ID,action) VALUES(SYSDATE,’Trigger’,'END AFTER EACH ROW’);
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
INSERT into TAB_HIST(ACTION_DT,USER_ID,action) values(sysdate,’Trigger’,'START AFTER STATEMENT’);
INSERT into TAB_HIST(ACTION_DT,USER_ID,action) values(sysdate,’Trigger’,'END AFTER STATEMENT’);
END AFTER STATEMENT;
END TRG_TAB1_INSERT;
– Test execution of the inserts in loop
BEGIN
FOR i in 1..5
LOOP
INSERT INTO TAB1 (TAB1_KEY,ACTION_DT,ACTION) values(123,sysdate,’Tested’);
END LOOP;
COMMIT;
END;
/
in 11gR2:
SQL> select count(*) from TAB_HIST;
COUNT(*)
———-
40
in 11.1.0.7
SQL> select count(*) from TAB_HIST;
COUNT(*)
———-
8
11.1.0.7, version has lot of know bug with compound triggers.
This issue is fixed in below patch,
Patch 8850303: ORA-600 [PEVMEXE.C: BREAK: BUG 281522 : HIT OPCODE BREAK] – COMPOUND TRIGGER
convert this post to pdf.