CODEWITHSUNDEEP
sqloracle-databaseplsqltriggers
AWS Cloud Architect Rob
AWS Cloud Architect Rob

Reputation: 614

SQL Trigger WHEN Clause compilation error

I am trying to create a trigger that will check that the date entered is in range if the Prog_Type entered is 'FILM', however I receive a compilation error every time the statement is run.

The error code is 00103, "Encountered a ; when expected ....."

then a list of many punctuation marks. From researching the problem I have seen references to a DECLARE statement, however I am not sure I would need to declare if the Prog_Type is created in the Program_Table, and I have reference that table in the trigger.

CREATE OR REPLACE TRIGGER Prog_Made_Limits
BEFORE INSERT OR UPDATE OF Prog_Made ON Program_Table
 FOR EACH ROW
  WHEN (NEW.Prog_Type <> 'FILM')
BEGIN
  IF (:new.Prog_Made < date '1864-12-31' or 
      :new.Prog_Made > sysdate );
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Program Made date must be later than Dec 31st 1864, and earlier than today, if FILM is inserted');
  END IF;
END Prog_Made_Limits;
/

Upvotes: 0

Views: 22758

Answers (2)

user2001117
user2001117

Reputation: 3777

Hey you have used the semicolon after the IF clause. That will end the statement. So please remove the column from that place and use the below code once:

CREATE OR REPLACE TRIGGER Prog_Made_Limits
BEFORE INSERT OR UPDATE OF Prog_Made ON Program_Table
 FOR EACH ROW
  WHEN (NEW.Prog_Type <> 'FILM')
BEGIN
  IF (:new.Prog_Made < date '1864-12-31' or 
      :new.Prog_Made > sysdate )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Program Made date must be later than Dec 31st 1864, and earlier than today, if FILM is inserted');
  END IF;
END Prog_Made_Limits;
/

Upvotes: 0

GarethD
GarethD

Reputation: 69759

Your syntax almost fine, but you have a ; after your IF condition which is causing the error. ; is used to separate statements and consequent of your If is part of the the same statement as the condition.

This should work:

CREATE OR REPLACE TRIGGER Prog_Made_Limits
BEFORE INSERT OR UPDATE OF Prog_Made ON Program_Table
 FOR EACH ROW
  WHEN (NEW.Prog_Type <> 'FILM')
BEGIN
  IF (:new.Prog_Made < date '1864-12-31' or 
      :new.Prog_Made > sysdate )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 'Program Made date must be later than Dec 31st 1864, and earlier than today, if FILM is inserted');
  END IF;
END Prog_Made_Limits;

I say should, because I don't have Oracle on my laptop, so can only test on SQL Fiddle, but by removing all semicolons I was able to get this to compile, however I think SQL-Fiddle treats semicolons as batch breaks rather than statement breaks, so I am not sure it is necessary to remove them all.

Example on SQL Fiddle

Upvotes: 4

Related Questions