Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create or replace type Tariff_Plan_TY as object(
  tariff_id INTEGER,
  call_price DECIMAL(3,2),
  sms_price DECIMAL(3,2)
)FINAL;
create or replace type Contract_TY as object(
  telephone_no VARCHAR(10),
  tariff_plan REF Tariff_Plan_TY
)NOT FINAL;
create or replace
type Operation_TY as object(
  id INTEGER,
  contract REF Contract_TY
)NOT FINAL;
create or replace type Call_TY UNDER Operation_TY(
  called_no VARCHAR(10),
  end_time TIMESTAMP WITH LOCAL TIME ZONE
)FINAL;
create or replace type SMS_TY UNDER Operation_TY(
  receiver_no VARCHAR(10)
)FINAL;
create table Contract_TB of Contract_TY;
create table Tariff_Plan_TB of Tariff_Plan_TY;
create table Operation_TB of Operation_TY;
INSERT INTO Tariff_Plan_TB
SELECT 1, 0.10, 0.01 FROM DUAL UNION ALL
SELECT 2, 0.20, 0.02 FROM DUAL;
2 rows affected

INSERT INTO Contract_TB
SELECT '1', ( SELECT REF(t) FROM Tariff_Plan_TB t WHERE tariff_id = 1 ) FROM DUAL UNION ALL
SELECT '2', ( SELECT REF(t) FROM Tariff_Plan_TB t WHERE tariff_id = 2 ) FROM DUAL;
2 rows affected

CREATE TABLE log_what_happened ( id INTEGER, type VARCHAR2(20), price DECIMAL(3,2) );
CREATE TRIGGER CHECK_OPERATION
BEFORE INSERT ON OPERATION_TB
FOR EACH ROW
DECLARE
  v_contract    contract_ty;
  v_tariff_plan tariff_plan_ty;
BEGIN
  SELECT DEREF( :NEW.contract )
  INTO   v_contract
  FROM   DUAL;

  SELECT DEREF( v_contract.tariff_plan )
  INTO   v_tariff_plan
  FROM   DUAL;

  IF :NEW.OBJECT_VALUE IS OF ( SMS_TY ) THEN
    INSERT INTO log_what_happened ( id, type, price )
      VALUES ( :NEW.ID, 'SMS', v_tariff_plan.sms_price );
  ELSIF :NEW.OBJECT_VALUE IS OF ( CALL_TY ) THEN
    INSERT INTO log_what_happened ( id, type, price )
      VALUES ( :NEW.ID, 'CALL', v_tariff_plan.call_price );
  ELSE
    INSERT INTO log_what_happened ( id, type, price )
      VALUES ( :NEW.ID, 'CONTRACT', NULL );
  END IF;
END;
/
SELECT * FROM USER_ERRORS;
INSERT INTO Operation_TB
SELECT Call_TY( 1, ( SELECT REF(c) FROM Contract_TB c WHERE telephone_no = '1' ), '555', SYSTIMESTAMP ) FROM DUAL UNION ALL
SELECT OPERATION_TY( 2, ( SELECT REF(c) FROM Contract_TB c WHERE telephone_no = '2' ) ) FROM DUAL UNION ALL
SELECT SMS_TY( 3, ( SELECT REF(c) FROM Contract_TB c WHERE telephone_no = '1' ), '777' ) FROM DUAL;
3 rows affected

SELECT * FROM log_what_happened;


ID
TYPE
PRICE


1
CALL
.1

2
CONTRACT
null

3
SMS
.01