How to know the type of :new in a trigger?

Question

Let's suppose that I have these dummy types and tables of these types:

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;

Let's suppose also that I have different kinds of operations:

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;

I would like to create a trigger on a table of Operation_TY that will tell me how much the operation costs. To have this information, when an operation is inserted in the table, I would like to check the type of the operation and then go into the tariff_plan of the contract to find the price of the operation.

I tried with:

create or replace
TRIGGER CHECK_OPERATION
BEFORE INSERT ON OPERATION_TB
FOR EACH ROW
DECLARE
  contract contract_ty;
  tariff_plan tariff_plan_ty;
  operation ref operation_ty;
BEGIN
  operation = ref :new;
  SELECT deref(:new.contract) into contract from dual;
  SELECT deref(contract.tariff_plan) into tariff_plan from dual;

  if (operation is of (sms_ty)) then
    NULL; -- go search the price
  elsif (operation is of (call_ty)) then
    NULL; -- go search the price
  else
    NULL;
  end if;
END;

This doesn't work and I think I'm missing something. How should this operation be carryied out?

I leave here a small script to random populate the tables:

create table Contract_TB of Contract_TY;
/
create table Tariff_Plan_TB of Tariff_Plan_TY;
/
create table Operation_TB of Operation_TY;
/
create or replace PROCEDURE POPULATE_TARIFF_PLAN AS
idx INTEGER;  
BEGIN 
  idx := 0;
  LOOP 
    INSERT INTO tariff_plan_tb
    VALUES (
      idx,
      DBMS_RANDOM.VALUE,
      DBMS_RANDOM.VALUE
      ); 
  idx := idx + 1;
  EXIT WHEN idx = 10;
  end loop;
END POPULATE_TARIFF_PLAN;
/
create or replace
PROCEDURE populate_contract AS
idx INTEGER;
phone_no VARCHAR(10);
ref_tariff_plan REF Tariff_Plan_TY;
BEGIN
  idx := 0;
  SELECT TO_CHAR(
    TRUNC(DBMS_RANDOM.VALUE(1000000000, 9999999999))
  ) INTO phone_no FROM DUAL;

  SELECT * INTO ref_tariff_plan FROM(
    SELECT REF(T)
    FROM tariff_plan_tb T
    ORDER BY DBMS_RANDOM.VALUE
  ) WHERE rownum <= 1;

  INSERT INTO contract_tb
  VALUES (phone_no,
        ref_tariff_plan);
  idx := idx + 1;
  EXIT WHEN idx = 500;
  END LOOP;
END;
/

create or replace
procedure populate_operation as
idx NUMBER;
ref_contract ref Contract_TY;
begin
idx := 0;
loop 
  SELECT * into ref_contract FROM(SELECT ref(ct) FROM CONTRACT_TB ct ORDER BY dbms_random.value) WHERE rownum <= 1;
  INSERT INTO OPERATION_TB values(
      SMS_TY(
        idx,
        ref_contract
      ));
      idx := idx + 1;
    exit when idx = 1000;
end loop;
end;
/

Answer 1

Use the OBJECT_VALUE pseudo-column to get the row object.

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;
/

Which, if you have the table:

CREATE TABLE log_what_happened (
  id    INTEGER,
  type  VARCHAR2(20),
  price DECIMAL(3,2)
);

and insert the data into your tables:

INSERT INTO Tariff_Plan_TB
SELECT 1, 0.10, 0.01 FROM DUAL UNION ALL
SELECT 2, 0.20, 0.02 FROM DUAL;

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;

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;

Then:

SELECT * FROM log_what_happened;

Outputs:

ID | TYPE     | PRICE
-: | :------- | ----:
 1 | CALL     |    .1
 2 | CONTRACT |  null
 3 | SMS      |   .01

db<>fiddle here