SQL & PL/SQL

Hello every one,

I am working on a Trigger for my little Oracle XE database.

Here are some informations about my database:

Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

My table looks like this:

CREATE TABLE TRIGGERTEST
   (
      ORDERID NUMBER(38,0) NOT NULL, 
      STARTDATE TIMESTAMP (6), 
      ENDDATE TIMESTAMP (6), 
      PAYDATE TIMESTAMP (6), 
      CUSTOMERID NUMBER(38,0) NOT NULL, 
      CONSTRAINT TRIGGERTEST_PK PRIMARY KEY (ORDERID)
   );

And now comes my problem :)
- I try to create a trigger, which allows to insert a record into the table
- but only IF the customer (customerid) has no orders
- OR the ENDDATE of his order is > sysdate!
- this means that a customer can only have 1 order in the same time.
You can compare it with the implementing of premium and normal accounts. You have to pay to get a premium account and for this time you cant buy again a premium account (only if the end date (ENDDATE) of your premium account is less than current date (sysdate or localtimestamp).

I'm sorry for my bad englisch and my PL/SQL knowledge are not so good!

I try to create a trigger like this one:

create or replace
TRIGGER proof_custid_trg
BEFORE INSERT ON triggertest
  FOR EACH ROW
BEGIN
  IF :NEW.startdate >= :NEW.enddate THEN
    raise_application_error (-20901, 'Startdate >= Enddate');
  ELSIF :NEW.enddate >= LOCALTIMESTAMP 
        AND :NEW.startdate >= :OLD.startdate OR :NEW.startdate <= :OLD.startdate
        AND :NEW.enddate <= :OLD.enddate OR :NEW.enddate >= :OLD.enddate
        AND :OLD.customerid = :NEW.customerid THEN
   raise_application_error (-20902, 'Customer alredy has a premium Account.');
  END IF;
END;

I know, a lot of you will laugh about me :)) but I just try it :)
The trigger is not realy working! I think he ignoere this part -> "AND :OLD.customerid = :NEW.customerid THEN" which is one of the important.

I will be very happy if some one can help me!!

best regards
nedo