Error with the trigger. How to do

oracle

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?

Best Answer

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;
Related Question