asked on

Instead of insert question

I am trying to write an instead of insert trigger

-- INSTEAD OF insert on Tertiary Contact views
      INSERT INTO GWTSDEV.dbcontactperson
           (CONTACTID,
           CONTACTTYPE,
            TAXID,
            FIRSTNAME,
            LASTNAME)
     VALUES
           (:contacts.contactid,
:contacts.contacttype,
            :contacts.taxid,
            :contacts.firstname,
            :contcts.lastname);
     commit;
WHEN duplicate_info THEN
RAISE_APPLICATION_ERROR (
num => -21107,
msg => 'Duplicated Record');
END;

When I use the following code it does not like the 'When' , when I compile. If I use the code below it will compile but when I run the trigger I get FRM-40735:ON-INSERT trigger raised unhandled exception. ORA -00001.

-- INSTEAD OF insert on Tertiary Contact views
      INSERT INTO GWTSDEV.dbcontactperson
           (CONTACTID,
           CONTACTTYPE,
            TAXID,
            FIRSTNAME,
            LASTNAME)
     VALUES
           (:contacts.contactid,
:contacts.contacttype,
            :contacts.taxid,
            :contacts.firstname,
            :contcts.lastname);
     commit;
END;

SOLUTION

concon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

uTab

ASKER

Hi concon,

Now it says that the 'FORM_TRIGGER_FAILURE' is not a proceedure or undefined.

Thanks

pennnn

You should use raise_application_error (as you did in your original example), not form_trigger_failure. The form_trigger_failure can be used in Oracle Forms only. It is a predefined exception in Forms, the same way as duplicate_info is a predefined exception in the example from the Oracle SQL Reference manual:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_76a.htm#2064427

You should have copied the DECLARE section also, not only the exception section:
DECLARE
duplicate_info EXCEPTION;
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

Anyway, it's much cleaner to use concon's suggestion (when_dup_val_on_index).
Hope that helps!

uTab

ASKER

I am using Oracle Forms. The code is as follows:

EXCEPTION
when DUP_VAL_ON_INDEX then
Form_Trigger_Failure(
num => -21107,
msg => 'Duplicated Contact Information Record');
END;

ASKER CERTIFIED SOLUTION

pennnn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bolicat

when doing this

SQL> CREATE TRIGGER updDEPT_MAST
2 INSTEAD OF UPDATE OR INSERT ON vSEL_DEPT_MAST
3 REFERENCING NEW AS Inserted OLD AS Deleted
4 BEGIN
5 IF EXISTS(SELECT 1 FROM Deleted) then
6 UPDATE DEPT_MAST
7 SET DEPT_MAST.DEPT_NO = :Inserted.DEPT_NO
8 ,DEPT_MAST.DEPT_NAME = :Inserted.DEPT_NAME
9 FROM DEPT_MAST, Inserted
10 WHERE DEPT_MAST.IMAGE_RECNBR = :Inserted.IMAGE_RECNBR;
11 ELSE
12 INSERT INTO DEPT_MAST SELECT
13 DEPT_NO
14 ,DEPT_NAME
15 FROM Inserted
16 END
17 /

getting this

Warning: Trigger created with compilation errors.

SQL>
SQL> show errors trigger updDEPT_MAST;
Errors for TRIGGER UPDDEPT_MAST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/6 PL/SQL: SQL Statement ignored
6/11 PL/SQL: ORA-00933: SQL command not properly ended
13/3 PLS-00103: Encountered the symbol "end-of-file" when expecting
one of the following:
;

SQL>

uTab

ASKER

You are missing the ; on lines 15 and 16.