Oracle Database Discussions

Hi.

I have a question concerning compound triggers.
Using INSERT and UPDATE statements the timing points are entered as described in the reference.
Using MERGE, the BEFORE STATEMENT and AFTER STATEMENT will be parsed twice.
BUT BEFORE STATEMENT has not the effect, one would expect.

Please find my test scenario attached.

After executing the original please modify the following lines in compound trigger declaration:
1) Eliminate both initializations in BEFORE STATEMENT
--> Nearly the same result, but exception is thrown during first call.
2) Do the initialization in the declaration area of the compound trigger (you will find it as comment)
--> everything works fine.

Is there anyone who can explain the logic of timing points using merge scripts?

Thanks in advance,
Petra

## EXAMPLE:
DROP TABLE compound_test_trace;
DROP SEQUENCE compound_test_trace_seq;
DROP TABLE compound_test;


CREATE TABLE compound_test_trace
(
id NUMBER(12) not null,
TRACE VARCHAR2(400)
);

CREATE SEQUENCE compound_test_trace_seq
INCREMENT BY 1 START WITH 1 NOMAXVALUE;

create or replace trigger compound_test_trace
BEFORE INSERT
on compound_test_trace
for each row
WHEN ( NEW.ID IS NULL)
DECLARE
BEGIN
SELECT compound_test_trace_seq.NEXTVAL INTO :NEW.ID FROM DUAL;
END;
/
CREATE TABLE compound_test
(
id NUMBER(12) not null,
DESCRIPTION VARCHAR2(200)
);

alter table compound_test
add constraint XPKcompound_test primary key (id);


CREATE OR REPLACE TRIGGER TAIUC_compound_test
FOR INSERT OR UPDATE ON compound_test
COMPOUND TRIGGER
TYPE TABLE_VARCHAR2 IS TABLE OF VARCHAR2(32767);
TYPE TABLE_NUMBER IS TABLE OF NUMBER;


v_list_description TABLE_VARCHAR2 ; --:= NEW TABLE_VARCHAR2();
v_list_ID TABLE_NUMBER ; --:= NEW TABLE_NUMBER();
i PLS_INTEGER;

PROCEDURE TRACE (i_desc VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES (i_desc);
COMMIT COMMENT 'AUTONOMOUS_TRANSACTION';

EXCEPTION
WHEN OTHERS THEN
NULL;
END;

BEFORE STATEMENT IS
BEGIN
TRACE('BEFORE STATEMENT');
v_list_description := NEW TABLE_VARCHAR2();
v_list_ID := NEW TABLE_NUMBER();
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
TRACE('BEFORE EACH ROW');

BEGIN
v_list_description.extend();
v_list_description(v_list_description.last) := :NEW.DESCRIPTION;

v_list_ID.extend();
v_list_ID(v_list_ID.last) := :NEW.ID;
END;
END BEFORE EACH ROW;

AFTER EACH ROW IS
BEGIN
TRACE('AFTER EACH ROW');
NULL;
END AFTER EACH ROW;


AFTER STATEMENT IS
BEGIN
TRACE('AFTER STATEMENT');

IF (NVL(CARDINALITY(v_list_description),0) > 0)
THEN
i := v_list_description.first;
WHILE i IS NOT NULL
LOOP
i := v_list_description.next(i);
END LOOP;

i := v_list_ID.first;

WHILE i IS NOT NULL
LOOP
TRACE('**ID: ' || v_list_ID(i) ||' - Description: ' || v_list_description(i));
i := v_list_ID.next(i);
END LOOP;
END IF;
END AFTER STATEMENT;

END TAIUC_compound_test;
/
BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Insert1');
END;
/
INSERT INTO compound_test (ID,DESCRIPTION) VALUES (1,'Beschreibung 1');

BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Insert2');
END;
/
INSERT INTO compound_test (ID,DESCRIPTION) VALUES (2,'Beschreibung2');

BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Update2');
END;
/
UPDATE compound_test SET DESCRIPTION = 'Beschreibung 2' WHERE ID = 2;


BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Merge - keine Änderung');
END;
/
MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Beschreibung 1' FROM DUAL
UNION SELECT 2, 'Beschreibung 2' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)
;

BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Merge - Insert 3+4');
END;
/
MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Beschreibung 1' FROM DUAL
UNION SELECT 2, 'Beschreibung 2' FROM DUAL
UNION SELECT 3, 'Beschreibung 3' FROM DUAL
UNION SELECT 4, 'Beschreibung 4' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)
;

BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Merge - Update 1');
END;
/
MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Beschreibung1' FROM DUAL
UNION SELECT 2, 'Beschreibung 2' FROM DUAL
UNION SELECT 3, 'Beschreibung 3' FROM DUAL
UNION SELECT 4, 'Beschreibung 4' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)
;

BEGIN
INSERT INTO compound_test_trace(TRACE) VALUES ('**Merge - Update 1-4');
END;
/
MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Description 1' FROM DUAL
UNION SELECT 2, 'Description 2' FROM DUAL
UNION SELECT 3, 'Description 3' FROM DUAL
UNION SELECT 4, 'Description 4' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)
;


select * FROM compound_test_trace ORDER BY ID ASC;


## OUTPUT:
Table dropped

Sequence dropped

Table dropped

Table created

Sequence created

Trigger created

Table created

Table altered

Trigger created

PL/SQL procedure successfully completed

1 row inserted

PL/SQL procedure successfully completed

1 row inserted

PL/SQL procedure successfully completed

1 row updated

PL/SQL procedure successfully completed

0 rows merged

PL/SQL procedure successfully completed

MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Beschreibung 1' FROM DUAL
UNION SELECT 2, 'Beschreibung 2' FROM DUAL
UNION SELECT 3, 'Beschreibung 3' FROM DUAL
UNION SELECT 4, 'Beschreibung 4' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)

ORA-06531: Nicht initialisierte Zusammenstellung referenziert
ORA-06512: in "BBS.TAIUC_COMPOUND_TEST", Zeile 34
ORA-04088: Fehler bei der Ausführung von Trigger 'BBS.TAIUC_COMPOUND_TEST'

PL/SQL procedure successfully completed

MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Beschreibung1' FROM DUAL
UNION SELECT 2, 'Beschreibung 2' FROM DUAL
UNION SELECT 3, 'Beschreibung 3' FROM DUAL
UNION SELECT 4, 'Beschreibung 4' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)

ORA-06531: Nicht initialisierte Zusammenstellung referenziert
ORA-06512: in "BBS.TAIUC_COMPOUND_TEST", Zeile 34
ORA-04088: Fehler bei der Ausführung von Trigger 'BBS.TAIUC_COMPOUND_TEST'

PL/SQL procedure successfully completed

MERGE INTO compound_test a
USING
(
SELECT *
FROM (SELECT ID, DESCRIPTION FROM compound_test WHERE rownum < 1
UNION SELECT 1, 'Description 1' FROM DUAL
UNION SELECT 2, 'Description 2' FROM DUAL
UNION SELECT 3, 'Description 3' FROM DUAL
UNION SELECT 4, 'Description 4' FROM DUAL
) v
WHERE (ID, nvl(DESCRIPTION, CHR(0)) )
NOT IN (SELECT ID,nvl(DESCRIPTION, CHR(0))
FROM compound_test
WHERE ID = v.id
)
) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET DESCRIPTION = b.description
WHEN NOT MATCHED THEN
INSERT ( ID, DESCRIPTION)
VALUES (b.ID, b.DESCRIPTION)

ORA-06531: Nicht initialisierte Zusammenstellung referenziert
ORA-06512: in "BBS.TAIUC_COMPOUND_TEST", Zeile 34
ORA-04088: Fehler bei der Ausführung von Trigger 'BBS.TAIUC_COMPOUND_TEST'

ID TRACE
------------- --------------------------------------------------------------------------------
1 **Insert1
2 BEFORE STATEMENT
3 BEFORE EACH ROW
4 AFTER EACH ROW
5 AFTER STATEMENT
6 **ID: 1 - Description: Beschreibung 1
7 **Insert2
8 BEFORE STATEMENT
9 BEFORE EACH ROW
10 AFTER EACH ROW
11 AFTER STATEMENT
12 **ID: 2 - Description: Beschreibung2
13 **Update2
14 BEFORE STATEMENT
15 BEFORE EACH ROW
16 AFTER EACH ROW
17 AFTER STATEMENT
18 **ID: 2 - Description: Beschreibung 2
19 **Merge - keine Änderung
20 BEFORE STATEMENT

ID TRACE
------------- --------------------------------------------------------------------------------
21 BEFORE STATEMENT
22 AFTER STATEMENT
23 AFTER STATEMENT
24 **Merge - Insert 3+4
25 BEFORE STATEMENT
26 BEFORE STATEMENT
27 BEFORE EACH ROW
28 **Merge - Update 1
29 BEFORE STATEMENT
30 BEFORE STATEMENT
31 BEFORE EACH ROW
32 AFTER EACH ROW
33 BEFORE EACH ROW
34 **Merge - Update 1-4
35 BEFORE STATEMENT
36 BEFORE STATEMENT
37 BEFORE EACH ROW
38 AFTER EACH ROW
39 BEFORE EACH ROW
40 AFTER EACH ROW
41 BEFORE EACH ROW

41 rows selected