SQL: Creating a trigger before subtype insert

user_11356153 0
Viewed 69

So I'm dealing with a situation can be described as the following:
Basically, I have two types car and supercar

CREATE OR REPLACE TYPE CAR_T AS OBJECT (
  id VARCHAR (10),
  fuel_type VARCHAR (10)
) NOT FINAL;

CREATE OR REPLACE TYPE SUPERCAR_T UNDER CAR_T (
  number_cylinders  number(10)
);

When inserting a supercar into car:

INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 12));

I'd like to check if the the number of cylinders is greater than 6 before inserting into CAR table WITHOUT CREATING A TABLE FOR SUPERCARS:

CREATE OR REPLACE TRIGGER INSERT_SUPERCAR
    BEFORE INSERT ON CAR
    REFERENCING NEW AS NEW
    FOR EACH ROW

    BEGIN
        IF :NEW.number_cylinders < 6 THEN
                RAISE_APPLICATION_ERROR(-20001,'Not a supercar');
    END;

I always end up with the following error:

bad bind variable 'NEW.number_cylinders'

It appears that I cannot access the attribute of the subtype directly. My question is how to fix such an error, and is there a better way to do it using check constraint without creating a new table for supercars?

plsql oracle sql
1 Answers

You can use the :NEW.OBJECT_VALUE pseudo-column and the TREAT function to cast the object to a SUPERCAR_T type and then check the number_cylinders attribute:

CREATE OR REPLACE TRIGGER INSERT_SUPERCAR
  BEFORE INSERT ON CAR
  REFERENCING NEW AS NEW
  FOR EACH ROW
BEGIN
  IF :NEW.OBJECT_VALUE IS OF (SUPERCAR_T)
      AND TREAT(:NEW.OBJECT_VALUE AS SUPERCAR_T).number_cylinders < 6
  THEN
    RAISE_APPLICATION_ERROR(-20001,'Not a supercar');
  END IF;
END;
/

Then:

INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 12));

Works but:

INSERT INTO CAR VALUES(SUPERCAR_T(99, 'petrol', 4));

Raises the exception:

ORA-20001: Not a supercar

You could also use a CHECK constraint:

ALTER TABLE car ADD CONSTRAINT car__supercar_cylinders__chk CHECK (
  OBJECT_VALUE IS NOT OF (SUPERCAR_T)
  OR TREAT(OBJECT_VALUE AS SUPERCAR_T).number_cylinders >= 6
);

or

ALTER TABLE car ADD CONSTRAINT car__supercar_cylinders2__chk CHECK (
  TREAT(OBJECT_VALUE AS SUPERCAR_T).number_cylinders IS NULL
  OR TREAT(OBJECT_VALUE AS SUPERCAR_T).number_cylinders >= 6
);

db<>fiddle here

user_15092640
Related