How to reference custom type row in trigger

Question

I have created 2 custom types in SQL. The first one just a bare minimum type, for a concise example.

CREATE OR REPLACE TYPE TestType FORCE AS OBJECT
(
    title VARCHAR(50)
);
/

The second type is a kind of log of the first type. This type is used to log changes of the first type.

CREATE OR REPLACE TYPE LogType FORCE AS OBJECT 
(
  title VARCHAR(50),
  TestRef REF TestType
);
/

And the tables:

CREATE TABLE TestTable OF TestType;
CREATE TABLE LogTable OF LogType;

The trigger:

CREATE TRIGGER UpdateLog
BEFORE UPDATE OF title ON TestTable
REFERENCING new as newrow old as oldrow
FOR EACH ROW
WHEN (newrow.title != oldrow.title)
BEGIN
  INSERT INTO LogTable VALUES(:oldrow.title, :newrow);
END UpdateLog;
/  

Now, as mentioned before, I would like to have trigger on the TestTable, which would watch on row changes. The following part works, the problem happens when I want to insert the changes to the log table. Inserting null instead of the reference works, but trying to insert something similar to :newrow or ref(:newrow) does not do it. It throws a bad bind variable (PLS-00049).

So the question is, how do i reference the newrow into the log table?

Answer 1

Use MAKE_REF and the pseudo-column OBJECT_ID:

CREATE OR REPLACE TRIGGER UpdateLog
BEFORE UPDATE OF title ON TestTable
REFERENCING new as newrow old as oldrow
FOR EACH ROW
WHEN (newrow.title != oldrow.title)
BEGIN
  INSERT INTO LogTable VALUES(:oldrow.title, make_ref(TestTable, :newrow.object_id));
END UpdateLog;
/