sql - Solution of this easy trigger

Question

I have a problem with my trigger(Im new with oracle) With this trigger I want when insert a new contract and this contracts have a start_date which is before that end_date of the other contract of this user, the trigger have to change the old end_date to the start_date of the new contract.

Here is my code:

CREATE OR REPLACE TRIGGER Date_contracts
    BEFORE INSERT OR UPDATE OF START_DATE ON CONTRACTS
    FOR EACH ROW
    BEGIN
      IF :NEW.START_DATE < (SELECT DISTINCT END_DATE FROM CONTRACTS WHERE SSN = NEW.SSN) 
      THEN OLD.END_DATE := NEW.START_DATE;
      END IF;
    END;
    /

And sql developres says that subquery not allowed in this context. Im sure that is to repair but I cant solve it because my level is so low. I hope you help me:) Thanks

Answer 1

1.

SELECT DISTINCT END_DATE FROM CONTRACTS WHERE SSN = NEW.SSN May Return one or more record which may cause proble.

2.

selecting from same table within trigger on which trigger is created may cause problem.

Thanks