Creating Under 18 Trigger in SQL-CodePudding

I'm trying to set an age verification trigger. If a job applicant is under 18, it should send an error message. I'm supposed to declare a variable of type date "difference date" and assign difference to the difference in years from today to the dob of the person using

SELECT extract(year from sysdate)-extract(year from dob) INTO difference FROM Person WHERE ID= :NEW.p_id;

So far this is what I have:

CREATE OR REPLACE TRIGGER application_age_check
BEFORE INSERT or UPDATE OF p_id
ON APPLICATION
FOR EACH ROW
BEGIN
DECLARE @difference AS INT = 
    SELECT extract(year from sysdate)-extract(year from dob) INTO difference
    FROM Person 
    WHERE ID= :NEW.p_id;
    IF (difference < 18);
    THEN
        raise_application_error(-20000, 'Age can not be less than 18');
    END IF;
END;

I keep getting errors when declaring the difference variable. I tried like a million different ways, and I can't seem to figure out what I'm doing wrong. Please be patient with me because I haven't worked too much in SQL and I'm still learning.

Thanks.

CodePudding user response：

If you use Oracle, then use its syntax.

Sample tables:

SQL> CREATE TABLE person
  2  (
  3     id    NUMBER,
  4     dob   DATE
  5  );

Table created.

SQL> CREATE TABLE application
  2  (
  3     p_id   NUMBER
  4  );

Table created.

SQL> INSERT ALL
  2    INTO person (id, dob)
  3  VALUES (1, DATE '2000-05-18')   --> older than 18
  4    INTO person (id, dob)
  5  VALUES (2, DATE '2010-03-19')   --> younger than 18
  6     SELECT * FROM DUAL;

2 rows created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER application_age_check
  2     BEFORE INSERT OR UPDATE OF p_id
  3     ON application
  4     FOR EACH ROW
  5  DECLARE
  6     difference  NUMBER;
  7  BEGIN
  8     SELECT EXTRACT (YEAR FROM SYSDATE) - EXTRACT (YEAR FROM p.dob)
  9       INTO difference
 10       FROM person p
 11      WHERE p.id = :new.p_id;
 12
 13     IF (difference < 18)
 14     THEN
 15        raise_application_error (-20000, 'Age can not be less than 18');
 16     END IF;
 17  END;
 18  /

Trigger created.

Testing:

SQL> INSERT INTO application (p_id) VALUES (1);

1 row created.

SQL> INSERT INTO application (p_id) VALUES (2);
INSERT INTO application (p_id) VALUES (2)
            *
ERROR at line 1:
ORA-20000: Age can not be less than 18
ORA-06512: at "SCOTT.APPLICATION_AGE_CHECK", line 11
ORA-04088: error during execution of trigger 'SCOTT.APPLICATION_AGE_CHECK'


SQL>

CodePudding user response：

Use the correct Oracle syntax and do not compare on only the years, use the MONTHS_BETWEEN function to compare the full dates:

CREATE TRIGGER application_age_check
  BEFORE INSERT OR UPDATE OF p_id
  ON application
  FOR EACH ROW
DECLARE
  v_dob person.dob%TYPE;
BEGIN
  SELECT dob
  INTO   v_dob
  FROM   person
  WHERE  id = :new.p_id;

  IF MONTHS_BETWEEN(SYSDATE, v_dob) < 18*12 THEN
    RAISE_APPLICATION_ERROR(-20000, 'Too young');
  END IF;
END;
/

db<>fiddle here