0

This trigger fails when it's called:

create or replace TRIGGER UNIQUE_PHYSICIAN_TRG 
    BEFORE INSERT ON UNIQUE_PHYSICIAN 
    FOR EACH ROW 
    BEGIN
      <<COLUMN_SEQUENCES>>
      BEGIN
        IF INSERTING AND :NEW.UNIQUE_ID IS NULL THEN
          SELECT UNIQUE_PHYSICIAN_SEQ.NEXTVAL INTO :NEW.UNIQUE_ID FROM SYS.DUAL;
        END IF;
        IF INSERTING AND :NEW.FSA IS NULL THEN
          SELECT SUBSTRING(inserted.POSTAL_CODE, 0, 3) INTO :NEW.FSA FROM INSERTED;
    END IF;
      END COLUMN_SEQUENCES;
    END;

The line:

  SELECT SUBSTRING(inserted.POSTAL_CODE, 0, 3) INTO :NEW.FSA FROM INSERTED;

seems to be the root of the problem. Is something wrong syntactically?

1
  • Side note : IF INSERTING is absolute redundant in this case. The trigger as it is fires only on inserts.
    – a1ex07
    Commented Jun 12, 2015 at 18:55

1 Answer 1

1

Oracle doesn't have an inserted (or a deleted or an updated) table. That's something that SQL Server provides in a trigger. The Oracle substring function is also substr not substring.

My guess is that you just want

IF :new.fsa IS NULL
THEN
  :new.fsa := SUBSTR(:new.POSTAL_CODE, 0, 3);
END IF;

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.