Can I copy: OLD and: new pseudo-records into / into Oracle stored procedure?

Question

I have an AFTER INSERT OR UPDATE OR DELETE trigger that I write to store every revision of a record that appears in a specific table, copying the INSERT and UPDATE :NEW values ​​to the mirror table, and for DELETE values :OLD .

I could significantly clutter my code by conditionally passing a record :NEW or :OLD to a procedure that would then insert into my history table. Unfortunately, I cannot find a way to transfer the whole record :OLD or :NEW .

Am I missing something or is there no way to avoid listing all the columns :NEW and :OLD when I call my insert procedure?

I want to do the following:

 DECLARE PROCEDURE LOCAL_INSERT(historyRecord in ACCT.ACCOUNTS%ROWTYPE) IS BEGIN INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (historyRecord.ID, historyRecord.NAME, historyRecord.DESCRIPTION, SYSDATE); END; BEGIN IF INSERTING OR UPDATING THEN LOCAL_INSERT(:NEW); ELSE --DELETING LOCAL_INSERT(:OLD); END IF; END; 

But I am stuck with this:

 DECLARE PROCEDURE LOCAL_INSERT(id in ACCT.ACCOUNTS.ID%TYPE, name in ACCT.ACCOUNTS.NAME%TYPE, description in ACCT.ACCOUNTS.DESCRIPTION%TYPE) IS BEGIN INSERT INTO ACCT.ACCOUNTS_HISTORY (ID, NAME, DESCRIPTION, DATE) VALUES (id, name, description, SYSDATE); END; BEGIN IF INSERTING OR UPDATING THEN LOCAL_INSERT(:NEW.ID, :NEW.NAME, :NEW.DESCRIPTION); ELSE --DELETING LOCAL_INSERT(:OLD.ID, :OLD.NAME, :OLD.DESCRIPTION); END IF; END; 

Okay, so this does not look like a big difference, but this is just an example with three columns, not tens.

Answer 1

This is not true. You must do it yourself through the listing.

Reasons why it cannot / does not work include:

• :old and :new - default conventions; you can name the links :old and :new what you want through the REFERENCING clause of the CREATE TRIGGER statement.

• you will need a public type declaration (via CREATE TYPE or through a package declaration) to be able to use it as an argument for another piece of code.

• trigger code is interpreted by code, not compiled code.

Answer 2

I do not think so is possible. The documentation does not mention anything like this.

This will certainly cost performance, but you can try to define your AFTER INSERT trigger and another BEFORE UPDATE OR DELETE , and do something like this in the trigger:

 SELECT * INTO rowtype_variable FROM accounts WHERE accounts.id = :NEW.id; -- :OLD.id for UPDATE and DELETE 

and then call your procedure with rowtype_variable .

Answer 3

Use SQL to generate SQL;

 select ' row_field.'||COLUMN_NAME||' := :new.'||COLUMN_NAME||';' from ALL_TAB_COLUMNS cols where cols.TABLE_NAME = 'yourTableName' order by cols.column_name. 

Then copy and paste the output.

Answer 4

If you use the AFTER trigger, you can use the rowid parameter as a parameter to the call procedure

 insert into t_hist select * from t where rowid = r; 

If you use the BEFORE trigger, you will get table ORA-04091 , BUT , which you can solve ( http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm ):

• Do not use triggers. The best way to avoid a table change error is to not use triggers. Although object-oriented Oracle provides "table-related" methods, most experienced PL / SQL developers avoid triggers if absolutely necessary.
• Use trigger after or instead. If you must use a trigger, it is best to avoid table errors using the after trigger to avoid currency problems associated with the mutation table. For example, with the "after upgrade to xxx" trigger, the initial update is complete and the table will not mutate.
• Repeat the work with trigger syntax - Dr. Hall has wonderful notes on mutating table errors and offers other ways to avoid mutating tables using a combination of level triggers and a trigon.
• Use offline transactions. You can avoid the error with the modified table by marking your trigger as a stand-alone transaction, making it independent of the table invoking the procedure.