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
)

Answer 1

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.