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):
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;

show error(assuming you're using SQL*Plus) show you is the actual compilation error? Nothing jumps out at me.updatestatement. There may be other compilation errors as well.show errorsshows 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.