[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

error during trigger creation

Posted on 2011-10-25
5
Medium Priority
?
227 Views
Last Modified: 2012-06-21
I'm trying to have a trigger update a record in table B whenever there is a insert on table A.However, keep getting error ORA-00904: invalid identifier

what is wrong with this trigger

create or replace trigger ATTACH

AFTER insert ON table_A
FOR EACH ROW
BEGIN
update  table_B set file='Y' where table_A.sti_id=table_B.id;
END;
/

0
Comment
Question by:sikyala
5 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37026995
Hi sikyala,

I believe that 'ATTACH' is a reserved word.  Rename the trigger.


Kent
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 37026999
remove table_a inside the trigger.

...
AFTER insert ON table_A
FOR EACH ROW
BEGIN
update  table_B set file='Y' where :new.sti_id=table_B.id;
END;
/
0
 

Author Comment

by:sikyala
ID: 37027006
i took out the where clause and it created the trigger attach
0
 

Author Comment

by:sikyala
ID: 37027011
ah ok
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 37027088
use :old & :new aliases in your trigger instead of table name directly...
and I recomend prefix all your triggers with "TRG_" better use something like this

TRG_TABLE_BA_IUD

BA = Before After
IUD = Insert Update Delete



CREATE OR REPLACE TRIGGER TRG_TABLE_A_A_I
   AFTER INSERT
   ON table_A
   FOR EACH ROW
BEGIN
   UPDATE table_B
      SET file = 'Y'
    WHERE table_B.id = :new.sti_id;
END;
/

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question