Borndba

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.