A Trigger which will implement update cascade to PK

Question

I need to write a trigger which will implement update cascade for (gameno) code for SummerGames but I keep encountering `Error(11,1): PLS-00103: Encountered the symbol "CREATE

I'm using Oracle SQL Developer.

My current code:

create or replace TRIGGER SG_GAMENO_UPDATE 
BEFORE UPDATE OF SG_GAMENO ON SUMMERGAMES 
FOR EACH ROW 
BEGIN
  UPDATE SUMMERGAMES
  SET SG_GAMENO = :new.SG_GAMENO
  WHERE SG_GAMENO = :old.SG_GAMENO;
END;

The error suggests you're running multiple commands as a script, and haven't separated them properly. Any PL/SQL block, inlcuding trigger creation, has to have a `/` after it to execute it, so it may be the statement after this one that is the problem. (This doesn't have 11 lines, which is a clue). Your trigger is updating the same table - and column - that was being updated anyway, so I don't understand what you're trying to do. And PKs are supposed to be immutable... — Alex Poole, Sep 14 '14 at 08:11
Yes, that makes more sense (as a project). The missing `/` was your original problem though. — Alex Poole, Sep 14 '14 at 09:26

score 1 · Answer 1 · edited May 23 '17 at 12:05

You need to end the trigger statement, which is partly PL/SQL, with a / on a line on its own. That will end the statement and cause it to be run.

create or replace TRIGGER SG_GAMENO_UPDATE 
BEFORE UPDATE OF SG_GAMENO ON SUMMERGAMES 
FOR EACH ROW 
BEGIN
  UPDATE SUMMERGAMES
  SET SG_GAMENO = :new.SG_GAMENO
  WHERE SG_GAMENO = :old.SG_GAMENO;
END;
/

At the moment whatever is folowing this in your script - which seems to be another create statement - is being seen as part of the same trigger creation, and that keyword isn't valid within a block.

Your trigger doesn't seem to make any sense, and at best will lead to infinite recursion when you attempt an update (which will be detected and killed), but that's a separate issue. Perhaps you meant to update a child table, rather than the same table the trigger is against. But you shouldn't really be updating a PK at all; that's why synthetic keys are generally preferred over natural ones. gameno sounds synthetic anyway.

score 0 · Answer 2 · answered Sep 14 '14 at 08:20

it will something about the separation of your codes, i mean when you executed the code snippet above, you marked off some part from another code, that's why you get syntactic error. try to execute just the code above, delete anyting else from the sql editor

A Trigger which will implement update cascade to PK

2 Answers2