Creating an Oracle trigger to stop people enrolling twice-CodePudding

I am facing some problems while I was trying to create a trigger. So the question I was giving is

The event holder has noted that several participants have enrolled in multiple events in the same exhibition which they do not wish to occur. Write a trigger to prevent this issue in future exhibition.

What I have written is

CREATE OR REPLACE TRIGGER check_event_enrolment BEFORE 
INSERT ON entry 
FOR EACH ROW 
DECLARE new_exhibiton_date DATE; 
        new_part_no NUMBER;
BEGIN
     new_carn_date := (SELECT to_char(carn_date, 'DD/MM/YYYY')
                         FROM event
                        WHERE event_id = :new.event_id);

     IF (new_carn_date IN ( SELECT TO_CHAR ( carn_date , 'DD/MM/YYYY' ) 
                              FROM entry
                            NATURAL JOIN event
                              WHERE part_no = :new.part_no ) ) THEN 
        raise_application_error (-20001, 'The participant has already enroled under the same exhibition' ) ; 
END IF; 
END;

Assume that an exhibition can have multiple events going on, the line new_carn_date := (SELECT to_char(carn_date, 'DD/MM/YYYY') FROM event WHERE event_id = :new.event_id); is giving me error and I dont know how to fix it.

Thank you in advance for your help. If you need more background information I will be more than happy to provide it.

CodePudding user response：

The syntax you are using is incorrect. The correct syntax sold be -

CREATE OR REPLACE TRIGGER check_event_enrolment BEFORE 
INSERT ON entry 
FOR EACH ROW 
DECLARE new_exhibiton_date DATE; 
        new_part_no NUMBER;
BEGIN
     SELECT to_char(carn_date, 'DD/MM/YYYY')
       INTO new_carn_date
       FROM event
      WHERE event_id = :new.event_id);

     IF (new_carn_date IN ( SELECT TO_CHAR ( carn_date , 'DD/MM/YYYY' ) 
                              FROM entry
                            NATURAL JOIN event
                              WHERE part_no = :new.part_no ) ) THEN 
        raise_application_error (-20001, 'The participant has already enroled under the same exhibition' ) ; 
END IF; 
END;

CodePudding user response：

Don't use a trigger, use a UNIQUE index:

CREATE UNIQUE INDEX entry__carn_dt__part__no__uidx
  ON entry (event_id, TRUNC(carn_date), part_no);

If you must use a trigger (don't) then:

CREATE OR REPLACE TRIGGER check_event_enrolment
  BEFORE INSERT ON entry 
  FOR EACH ROW 
DECLARE
  num_matches PLS_INTEGER;
BEGIN
  SELECT COUNT(*)
  INTO   num_matches
  FROM   entry
  WHERE  TRUNC(carn_date) = TRUNC(:NEW.carn_date)
  AND    event_id  = :NEW.event_id
  AND    part_no   = :NEW.part_no;
  
  IF num_matches > 0 THEN 
    raise_application_error(
      -20001,
      'The participant has already enrolled under the same exhibition'
    ) ; 
  END IF; 
END;
/