Home -> Usenet -> comp.databases.oracle -> HELP with TRIGGER

HELP with TRIGGER

From: Dickson Lukose <lukose_at_neumann.une.edu.au>
Date: 1995/08/24
Message-ID: <41gjli$lbb_at_grivel.une.edu.au>#1/1 






Dear Oracle Expert,



        I am very new to ORACLE. I need help with the following error 
that I am geeting when I run a "small" TRIGGER that I wrote the other day.



What is wrong with the TRIGGER ?



I have attached the SQL SCRIPT file, as well as the error that Iam getting when
I attempt to delete tuples from relation a3.




Any pointers or help will be most appreciated.



thanks in advance


D. Lukose

===============================SQL SCRIPT FILE================================





drop trigger a2Nulify;



drop table a4;


drop table a3;



create table a3 (

        customer_number number,
        customer_address char(30),
        PRIMARY KEY (customer_number))




STORAGE (INITIAL 10K NEXT 10K)



;




create table a4 (

        Account_Number number PRIMARY KEY,
        customer_number number NULL REFERENCES a3, /* Same as ON DELETE RESTRICT
ED */
        Amount  number,
        Prefered_Dr char(20) UNIQUE)




STORAGE (INITIAL 10K NEXT 10K)



;




insert into a3 values (001, 'Sydney');
insert into a3 values (002, 'Melbourne');
insert into a3 values (003, 'Canberra');
insert into a3 values (004, 'Queensland');

insert into a4 values (1000, 001, 390, 'Martin');
insert into a4 values (1010, 002, 70, 'Robert');
insert into a4 values (1021, 004, 34, 'David');


insert into a4 values (1030, 002, 304, 'Tomas');
insert into a4 values (1041, 003, 134, 'Allan');




CREATE TRIGGER a2Nulify


BEFORE DELETE ON OPS$NEURAL.a3


FOR EACH ROW



BEGIN


        UPDATE OPS$NEURAL.a4
        set OPS$NEURAL.a4.CUSTOMER_NUMBER = NULL
        where a3.customer_number = a4.customer_number;


END;



/

==========================ORACLE ERROR=========================================


SQL> delete from a3 where customer_number = 2;
delete from a3 where customer_number = 2


            *


ERROR at line 1:

ORA-00904: invalid column name
ORA-06512: at line 2
ORA-04088: error during execution of trigger 'OPS$NEURAL.A2NULIFY'






SQL>



Received on Thu Aug 24 1995 - 00:00:00 CEST












Original text of this message