What data types are: OLD and: NEW variables in a trigger?

Question

Suppose you have a trigger on MY_CUSTOMER_TABLE and that it has a variable declared like MY_CUSTOMER_TABLE%ROWTYPE . How can I assign an OLD value to this variable?

 CREATE TRIGGER CUSTOMER_BEFORE BEFORE UPDATE ON MY_CUSTOMER_TABLE FOR EACH ROW DECLARE old_version MY_CUSTOMER_TABLE%ROWTYPE; BEGIN old_version := :OLD; /* Causes a PLS-00049 bad bind variable 'OLD' */ old_version := OLD; /* Causes a PLS-00201 identifier 'OLD' must be declared */ END; 

Edit:

To explain this, this happened because I use triggers to archive rows from MY_CUSTOMER_TABLE to MY_CUSTOMER_TABLE_HISTORY . Depending on the action being performed ( INSERT , UPDATE , DELETE ), I need all the fields from OLD or NEW :

 CREATE TRIGGER CUSTOMER_BEFORE BEFORE UPDATE ON MY_CUSTOMER_TABLE FOR EACH ROW DECLARE historical_record MY_CUSTOMER_TABLE_HISTORY%ROWTYPE; PROCEDURE copy ( source_record MY_CUSTOMER_TABLE%ROWTYPE, destination_record IN OUT MY_CUSTOMER_TABLE_HISTORY%ROWTYPE ) BEGIN destination_record.customer_id := source_record.customer_id; destination_record.first_name := source_record.first_name; destination_record.last_name := source_record.last_name; destination_record.date_of_birth := source_record.date_of_birth; END; BEGIN /* I didn't want to replicate the same assignment statements for each of the two cases: */ CASE WHEN INSERT OR UPDATING THEN copy( source_record => :NEW, destination_record => historical_record ); WHEN DELETING THEN copy( source_record => :OLD, destination_record => historical_record ); END CASE; /* Some other assignments to historical_record fields... */ INSERT INTO MY_CUSTOMER_TABLE_HISTORY VALUES historical_record; END; 

In this case, PL / SQL will not allow me to pass :OLD or :NEW to a procedure that MY_CUSTOMER_TABLE%ROWTYPE .

Answer 1

From what I can determine, determining that: NEW and: OLD is really a bit vague. I saw this referred to as a pseudo-entry reference. It would seem that instead of the actual rowtype with each column referenced within the rowtype, Oracle sets up a link to each individual column that you then reference using: NEW and: OLD. But as you will find out: NEW and: OLD, it seems, cannot be used on their own.

For example, here . (yes, I know this is a Java link, but see the comment on: OLD is not a valid link on its own.

I also found this block of notes SYS.DBMS_DEBUG , which implies that: NEW /: OLD is also not a valid binding.

- get_value and set_value now support binding names. Name binding should be - put in quotation marks and uppercase. Note that trigger bindings have - qualified names, i.e. ": NEW" is not a valid binding, but ": NEW.CLMN" is indeed.

Was this suggestion of using the AFTER trigger for you? From your example, it looks like there are no checks on the values ​​(realizing that you may not have used this in your example for simplicity).

I tried to imagine a way to create a public type "on the fly" (within your trigger) that matches your table row type using the all_tab_columns view, and then pasted all the values ​​into it, but couldn't completely wrap my head around the details about how it can slip out ... if it even works. And that probably ends up requiring more work than is required to record a historical record!

Answer 2

You can not. Typically, bad practice applies to all columns (e.g. SELECT *), and you must specify the columns you need.

Answer 3

In the docs, you'll see that: old and: new are column values, not row types. Therefore, you will have to manually create your string type.

 trigger .... l_row mytable%rowtype; begin l_row.column1 := :old.column1; l_row.column2 := :old.column2; ... archive_function(l_row); end;