Trigger Problem

I am most familiar with the MS SQL Server product, so writing for Oracle can be confusing, as things that work in SQL Server DON'T work in Oracle (IF EXISTS for example).

I wrote the following trigger, which will work in SQL SERVER, but does NOT work in Oracle, and I am not sure why. Any help is appreciated.

CREATE OR REPLACE TRIGGER dB_Name.Trigger_Name BEFORE INSERT
ON dB_Name.ORD1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
/* Part 1 - Create the View that pulls all TCTO ID's that neec to
be created */
CREATE OR REPLACE VIEW view1 AS
SELECT MTCHP_ID AS MTCHD_ID, MTCHP.part AS PART, Serial_TO AS Serial_TO, Serial_From AS Serial_From, New_Part, :New.Item_ID AS ItemID, :New.Serial AS SerialNo
FROM MTCHP
WHERE MTCHP.Part = :New.Part
AND Serial_From <= :New.Serial AND Serial_TO <= :New.Serial OR Serial_From IS NULL AND Serial_TO IS NULL

/* Part 2 - Create the View that pulls all of the Auth Codes fOR
the TCTO's discovered IN Part 1 */
CREATE OR REPLACE VIEW view2 AS
SELECT * FROM MTCHP WHERE MTCHD_ID IN (SELECT MTCHD_ID FROM view1) AND TCTO_Status = 'A'

/* Now, Insert the new TCTO's Into the table, the user will still
be required to choose whether to complete the TCTO, but the Order is created for them */
INSERT INTO MTCH2
(MTCHD_ID, Item_ID, Status, FROM_Part, FROM_Serial, New_Part, Created_UserID, Created_DateTime)
(SELECT view2.MTCHD_ID, view1.ITEMID, 'N', view1.part, view1.SERIALNo, view1.NEW_PART, :New.Created_UserID, sysdate FROM view1
JOIN view2 ON view1.MTCHD_ID = view2.MTCHD_ID WHERE Part = :New.Part AND view1.mtchd_id NOT IN (SELECT MTCHP_ID FROM MTCH2)) The following error is returned when trying to implement in TOAD: ORA-04079: invalid trigger specification

The following error is returned from SQL+: ERROR at line 7:
ORA-04079: invalid trigger specification

• Line 7 is the first Create or Replace line ****

If I run the SQL statements separatly - works perfectly. Each view gets created/replaced. The insert statement does exactly what I want it to. But when I try and implement the trigger, it errors.

TIA, Randy Received on Thu Oct 07 2004 - 11:00:59 CDT