Re: Trigger Problem

On 7 Oct 2004 09:00:59 -0700, randy_at_webtracersolutions.com (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

Pl/sql doesn't allow DDL. You would need to use EXECUTE IMMEDIATE to wrap your CREATE VIEW statements. However, if I would see you coding a CREATE VIEW inside a trigger, I would fire you on the spot. It is *extreemly* bad programming practice.
A better solution for this type of view would be to set up a package. The view is going to refer to the package. The package consists of the variables you are now using as bind variables, defined in the package body, and get and set functions defined both in the package spec and inplemented in the package body, to manipulate the variables. The view refers to the get functions.

--
Sybrand Bakker, Senior Oracle DBA