Re: Instead of trigger question

Emad-ud-deen Richard Leiman & Eva Fithria Leiman wrote:

> Dear group,
>
> Hi.
>
> I am experimenting with instead of triggers. In the code I wish to insert
> into a table that contains abstract data types. The abstract data types are
> object types are address, and phone_type.
>
> The phone_type object type looks like this:
> CREATE OR REPLACE TYPE phone_type AS OBJECT (
> phone_1 NUMBER (8, 0),
> phone_2 NUMBER (8, 0),
> phone_3 NUMBER (8, 0)
> )
> /
>
> The address object type looks like this:
> CREATE OR REPLACE TYPE address AS OBJECT (
> NAME VARCHAR2 (26),
> fathername VARCHAR2 (25),
> address VARCHAR2 (25),
> phone oracle_objects.phone_type
> );
> /
>
> As it is now I can compile the trigger with this code:
>
> CREATE OR REPLACE TRIGGER oracle_objects.student_instead_of_trigger
> INSTEAD OF INSERT
> ON oracle_objects.full_student_view
> REFERENCING NEW AS NEW OLD AS OLD
> FOR EACH ROW
> BEGIN
> DBMS_OUTPUT.put_line ('You are in the instead of trigger now.');
>
> INSERT INTO student
> (student_id,
> student_information
> )
> VALUES (:new.student_id,
> address ('Emad-ud-deen',
> 'Leiman',
> '123 Testing Street',
> phone_type (3213452,
> 9999999,
> 3333333
> )
> )
> );
> END;
> /
>
> My question is: How do I change this code so :new will be ok inside the
> address, and phone_type object types?
>
> I tried this code but Oracle returns this error: PLS-00049: bad bind
> variable 'NEW.NAME'
>
> CREATE OR REPLACE TRIGGER oracle_objects.student_instead_of_trigger
> INSTEAD OF INSERT
> ON oracle_objects.full_student_view
> REFERENCING NEW AS NEW OLD AS OLD
> FOR EACH ROW
> BEGIN
> DBMS_OUTPUT.put_line ('You are in the instead of trigger now.');
>
> INSERT INTO student
> (student_id,
> student_information
> )
> VALUES (:new.student_id,
> address (:new.name,
> :new.father.name,
> :new.address,
> phone_type (:new.phone_1,
> :new.phone_2,
> :new.phone_3
> )
> )
> );
> END;
> /
>
> Truly,
> Emad

Look at the bottom of this page:
http://www.psoug.org/reference/instead_of_trigger.html

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)