0

This is being done on Oracle 11, and I'm trying to create a trigger where if the incoming meets certain values, to update another table.

--CREATE TRIGGER ON ITX
CREATE OR REPLACE TRIGGER TRG_I_LAST_RECEIVED
  AFTER INSERT ON ITEM_TRANSACTION_LOG
    FOR EACH ROW
      WHEN (
        NEW.ITEM_CONDITION_ID =  5005                                               -- REPAIRED
        AND NEW.WORKCENTER_ID IN (5001,5006,5063,5000,5022,5062)   -- EWRC
        AND NEW.ORDER_ITEM_OPER_ID = 5009                                      -- Perform Exit Routing
      )
BEGIN
  UPDATE PART 
  SET LAST_REPAIRED = SYSDATE
  WHERE PART_NO = :NEW.ITEM_PART_NO
END;

When I do, it adds it, but I get this (ORA-24344):

oracle error screen

Now, I understand SQL fairly well (T-SQL) and maybe I'm missing something - can someone eyeball this and tell me why it's not valid?

EDIT: The issue was missing semi-colon at the end of the update statement. Oracle's semi-colon discrepencies drive me bonkers sometimes.

Here's the correct statement, and thanks to Justin Cave for finding it:

--CREATE TRIGGER ON ITX
CREATE OR REPLACE TRIGGER TRG_I_LAST_RECEIVED
  AFTER INSERT ON ITEM_TRANSACTION_LOG
    FOR EACH ROW
      WHEN (
        NEW.ITEM_CONDITION_ID =  5005                                               -- REPAIRED
        AND NEW.WORKCENTER_ID IN (5001,5006,5063,5000,5022,5062)   -- EWRC
        AND NEW.ORDER_ITEM_OPER_ID = 5009                                      -- Perform Exit Routing
      )
BEGIN
  UPDATE PART 
  SET LAST_REPAIRED = SYSDATE
  WHERE PART_NO = :NEW.ITEM_PART_NO;    --add that semicolon!
END;
6
  • What does show error (assuming you're using SQL*Plus) show you is the actual compilation error? Nothing jumps out at me. Commented Feb 18, 2016 at 21:26
  • Unfortunately I'm on the train for home right now. I can give it a look when i get home and can VPN in. Commented Feb 18, 2016 at 21:37
  • On second pass, you are missing the semicolon at the end of your update statement. There may be other compilation errors as well. Commented Feb 18, 2016 at 21:39
  • Thanks. I'll try that when I get home. If you submit that as an answer, and it's the problem, I'll mark yours as the correct answer Commented Feb 18, 2016 at 21:50
  • If that's what show errors shows the error to be, I'll post it as an answer. I don't like posting that as an answer without seeing the actual error because it's entirely possible that the compiler is finding an error that I'm not. Commented Feb 18, 2016 at 22:09

3 Answers 3

1

Your update statement is missing a semicolon at the end. Since it sounds like you come from a T-SQL background, in SQL Server, semicolons are generally optional. They're always required in Oracle

BEGIN
  UPDATE PART 
  SET LAST_REPAIRED = SYSDATE
  WHERE PART_NO = :NEW.ITEM_PART_NO;
END;
Sign up to request clarification or add additional context in comments.

Comments

0

I'm not sure but seems that you have to specify refenrece to :NEW
It should look like:

 CREATE OR REPLACE TRIGGER TRG_I_LAST_RECEIVED
      AFTER INSERT ON ITEM_TRANSACTION_LOG
        REFERENCING OLD AS OLD NEW AS NEW
        FOR EACH ROW...

In current case reference to OLD will be empty record because of current trigger type. If it would be an AFTER UPDATE trigger then reference to OLDwould be the value of the table record before update operation.

Comments

0

Most probably you have missed the ":" before "NEW.ITEM_CONDITION_ID" and others columns, and ofcourse as "Justin Cave" mentioned you need ";" after update statement.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.