MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Oracle Trigger on Merge

without comments

An interesting question came up today while discussing PL/SQL database triggers. Could you create a trigger on a MERGE statement, like this:

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE MERGE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

The answer is, no you can’t. It’ll raise an ORA-04073 error if you attempt it, like this:

BEFORE MERGE OF last_name ON contact
             *
ERROR at line 2:
ORA-04073: COLUMN list NOT valid FOR this TRIGGER TYPE

The only supported DML events are INSERT, UPDATE, and DELETE. The following DML trigger works against a MERGE statement. After all a MERGE statement is nothing more than an INSERT or UPDATE statement.

1
2
3
4
5
6
7
8

CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/

Complete Code Sample ↓

Expand this section to see the sample working code.

This script creates a CONTACT table, a row-level TRIGGER, a MERGE statement, and query to display the results.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66

-- Conditionally drop the table.
BEGIN
  FOR i IN (SELECT NULL FROM user_tables WHERE TABLE_NAME = 'CONTACT_MERGE') LOOP
    EXECUTE IMMEDIATE 'DROP TABLE contact_merge';
  END LOOP;
END;
/
 
-- Create the table.
CREATE TABLE contact_merge
( contact_id                  NUMBER
, member_id                   NUMBER       NOT NULL
, contact_type                NUMBER       NOT NULL
, first_name                  VARCHAR2(20) NOT NULL
, middle_name                 VARCHAR2(20)
, last_name                   VARCHAR2(20) NOT NULL
, created_by                  NUMBER       NOT NULL
, creation_date               DATE         NOT NULL
, last_updated_by             NUMBER       NOT NULL
, last_update_date            DATE
, CONSTRAINT contact_merge_pk PRIMARY KEY(contact_id));
 
-- Create the trigger to enforce hyphenated last names..
CREATE OR REPLACE TRIGGER contact_merge_t1
BEFORE INSERT OR UPDATE OF last_name ON contact_merge
FOR EACH ROW
WHEN (REGEXP_LIKE(NEW.last_name,' '))
BEGIN
  :NEW.last_name := REGEXP_REPLACE(:NEW.last_name,' ','-',1,1);
END contact_merge_t1;
/
 
-- Merge statement that violates business rule.
MERGE INTO contact_merge target
USING
( SELECT 2001 AS contact_id
  ,      1001 AS member_id
  ,      1001 AS contact_type
  ,'Catherine' AS first_name
  ,'' AS middle_name
  ,'Zeta Jones' AS last_name
  , 2 AS created_by
  , SYSDATE AS creation_date
  , 2 AS last_updated_by
  , SYSDATE AS last_update_date
FROM dual) SOURCE
ON (target.contact_id = SOURCE.contact_id)
WHEN MATCHED THEN
UPDATE SET target.last_updated_by = 3
WHEN NOT MATCHED THEN
INSERT VALUES
( SOURCE.contact_id
, SOURCE.member_id
, SOURCE.contact_type
, SOURCE.first_name
, SOURCE.middle_name
, SOURCE.last_name
, SOURCE.created_by
, SOURCE.creation_date
, SOURCE.last_updated_by
, SOURCE.last_update_date );
 
-- Query results.
SELECT first_name||DECODE(middle_name,NULL,' ',' '||middle_name||' ')||last_name AS full_name
FROM   contact_merge
WHERE  first_name = 'Catherine';

Written by maclochlainn

March 16th, 2010 at 10:40 pm

Posted in Oracle,pl/sql,sql

«
»