oracle trigger error

1
votes

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
)
4
sqloracletriggersora-00904ora-00933
Check out this post which has an example similar to what you are trying to do. – Bala R
the problem is I think the syntax is correct. I do not why oracle complains. – chnet
Can you post the create table statements too..? It'll help reproducing your exact issue.. – Rajesh Chamarthi
I posted them. Thank you – chnet
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

4 Answers

4
votes

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.

4
votes

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;
0
votes

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.

0
votes

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