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?
CodePudding user response:
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