oracle trigger error

Question

I have two tables, tableA and tableB. I want to set a trigger. Once an insert happens in tableA, it may trigger some events in tableB.

The two tables are as follows, for example,

tableA columns: (product_id, product_name, manufacture)
tableB columns: (buyer, product_id)

What I want to do is: after inserting a new row into table A, if its product_name is null, then trigger updates on tableB. Update tableB' product_id to this new inserted product_id if the rows in tableB has the same manufacture as new inserted manufacture.

CREATE TRIGGER t1     

AFTER INSERT ON tableA    
FOR EACH ROW WHEN (NEW.product_name is NULL)

BEGIN

    UPDATE tableB 
       SET tableB.product_id = :NEW.product_id 
     WHERE tableB.product_id IN (SELECT tableA.product_id 
                                   FROM tableA 
                                  WHERE tableA.manufacture = :NEW.manufacture);

END;

It always complains several errors in SQL developer:

Error(2,2): PL/SQL: SQL Statement ignored
Error(2,120): PL/SQL: ORA-00933: SQL command not properly ended
Error(2,36): PL/SQL: ORA-00904: "NEW"."product_id": invalid identifier
Error: PLS-00801: internal error [ph2csql_strdef_to_diana:bind]

update:

CREATE TABLE "tableA"
  (
    "PRODUCT_ID"      NUMBER PRIMARY KEY,
    "PRODUCT_NAME"    VARCHAR2(50 BYTE) DEFAULT NULL,
    "MANUFACTURE" VARCHAR2(50 BYTE) DEFAULT NULL
)

CREATE TABLE "tableB"
(
    "BUYER_ID"      NUMBER PRIMARY KEY,
    "PRODUCT_ID"    NUMBER DEFAULT NULL
)

Check out this post which has an example similar to what you are trying to do. — Bala R
– Bala R, Commented Apr 18, 2011 at 2:07
the problem is I think the syntax is correct. I do not why oracle complains. — chnet
– chnet, Commented Apr 18, 2011 at 2:19
Can you post the create table statements too..? It'll help reproducing your exact issue.. — Rajesh Chamarthi
– Rajesh Chamarthi, Commented Apr 18, 2011 at 2:27
It would probably be better to use a stored procedure to do the insert to A and conditional update to B. This kind of work is hard to trace back to a trigger if you have problems later. You also may have difficulty selecting from A inside the trigger - I don't think it's known whether the query on A will include the row currently being inserted, so Oracle won't let you to avoid ambiguity. (But might depend on version - not sure!) — Alex Poole
– Alex Poole, Commented Apr 18, 2011 at 15:17

Alex Poole · Accepted Answer · 2011-04-18 08:40:38Z

4

Are you getting all those errors at the same time, or different errors as you try different things? The ORA-00904 (and possibly associated ORA-00933) would appear if you omitted the : before NEW.product_id and the PLS-00801 could come from having a space in between
(i.e. : NEW.product_id. Not sure how you could get both at the same time.

As it's posted now it looks fine - do you still get the message Errors: check compiler log after TRIGGER T1 compiled - or are you looking at old errors in SQL Developer's compiler log window? If you aren't sure, right-click in the compiler log window and choose 'clear' before re-running, to see what errors (if any) are really being generated by the current code.

answered Apr 18, 2011 at 8:40

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Andrews · Accepted Answer · 2011-04-18 08:47:15Z

4

You have created the tables with mixed case names "tableA" and "tableB". This is generally a bad practice in Oracle, and leads to problems when referencing the tables in code because they must be referred to in the correct case, enclosed in double quotes:

CREATE TRIGGER t1     

AFTER INSERT ON "tableA"    
FOR EACH ROW WHEN (NEW.product_name is NULL)

BEGIN

    UPDATE "tableB" 
       SET "tableB".product_id = :NEW.product_id 
     WHERE "tableB".product_id IN (SELECT "tableA".product_id 
                                   FROM "tableA" 
                                  WHERE "tableA".manufacture = :NEW.manufacture);

END;

answered Apr 18, 2011 at 8:47

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

2 Comments

Alex Poole Over a year ago

Since the trigger won't compile with different errors like that, I'd assumed the table names were changed inconsistently for posting, and the real names must all be uppercase. Worth pointing out anyway of course.

Tony Andrews Over a year ago

Yes, it probably isn't the problem posted, but it was what happened when I tried to recreate the problem using the code posted. And those CREATE TABLE statements look like a copy and paste from SQL Developer, though perhaps the real table names have been typed in in mixed case afterwards.

user330315 · Accepted Answer · 2011-04-18 08:34:12Z

0

In SQL*Plus you need to terminate a CREATE TRIGGER statement with a / on a single line.

Depending on your SQL tool you might need to use some different way of setting an alternate delimiter.

answered Apr 18, 2011 at 8:34

user330315

Comments

BusinessWorker · Accepted Answer · 2012-04-16 12:36:27Z

0

Modify the where clause in your nested select statement from WHERE "tableA".manufacture = :NEW.manufacture to WHERE "tableA".manufacture = NEW.manufacture

answered Apr 16, 2012 at 12:36

BusinessWorker

213 bronze badges

Collectives™ on Stack Overflow

oracle trigger error

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related