oracle trigger error

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
)

---

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.

---

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;

---

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.

---

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