CODEWITHSUNDEEP
plsqltriggers
Summa Nura
Summa Nura

Reputation: 1

User defined exception in Oracle trigger

Here in the below trigger duplicate_info exception is used without raising it inside the begin block, I'm not sure how this works. There is no exception when others also but this duplicate_info exception works. Strange!

CREATE OR REPLACE TRIGGER ORDER_INFO_T
INSTEAD OF INSERT ON order_info
DECLARE
    duplicate_info EXCEPTION;
    PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
    INSERT INTO customers
    (customer_id, cust_last_name, cust_first_name)
    VALUES (
    :new.customer_id,
    :new.cust_last_name,
    :new.cust_first_name);

    INSERT INTO orders (order_id, order_date, customer_id)
    VALUES (
    :new.order_id,
    :new.order_date,
    :new.customer_id);
EXCEPTION
    WHEN duplicate_info THEN
        RAISE_APPLICATION_ERROR (
        num=> -20107,
        msg=> 'Duplicate customer or order ID');
END order_info_insert;
/

Upvotes: 0

Views: 2289

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132720

When a primary or unique constraint is violated, Oracle raises the error:

ORA-00001: unique constraint (schemaname.constraintname) violated

The trigger has defined the exception duplicate_info and then associated it with the ORA-00001 error via the pragma:

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

So this means that when Oracle raises error ORA-00001, the associated user-defined exception duplicate_info gets raised.

It's quite redundant though, because Oracle supplies anexception DUP_VAL_ON_INDEX for ORA-00001 already:

CREATE OR REPLACE TRIGGER ORDER_INFO_T
INSTEAD OF INSERT ON order_info
BEGIN
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
VALUES (
:new.customer_id,
:new.cust_last_name,
:new.cust_first_name);
INSERT INTO orders (order_id, order_date, customer_id)
VALUES (
:new.order_id,
:new.order_date,
:new.customer_id);
EXCEPTION
WHEN dup_val_on_index THEN
RAISE_APPLICATION_ERROR (
num=> -20107,
msg=> 'Duplicate customer or order ID');
END order_info_insert;

Upvotes: 2

Related Questions