Re: Trigger Problem

Web Tracer wrote:

> 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

Your code demonstrates a profound lack of having invested any serious amount of time studying Oracle Concepts, Oracle Architecture, and Oracle best practices.

It appears that the reason you find Oracle confusing is that you have not done a bit of reading on the subject and are just hacking away like a plumber trying to do brain surgery.

You can perform DDL in triggers with NDS but only a damned fool with a wish to prove incompetence would try it. What you are trying to do is completely unnecessary and proves a lack of understanding about how to build scalable high performance apps.

I would suggest that you post the business problem rather than your proposed solution and immediately enroll yourself in some Oracle classes.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)