Forum Stats

  • 3,838,105 Users
  • 2,262,332 Discussions
  • 7,900,506 Comments

Discussions

Trigger: Easier way of passing triggering row columns to a procedure

Devang Thakkar
Devang Thakkar Member Posts: 154
edited Feb 25, 2010 7:24AM in SQL & PL/SQL
Hi,
I am using Oracle 11g and I want to know if there is any better way of passing all the columns of the triggerring row to a procedure?

Consider the following:
Table: my_table -->this is a table with 200 columns
Trigger: my_trigger --> an after insert trigger on my_table
Procedure: my_proc (p_date my_table%ROWTYPE);

The conventional way would be to pass the values as

CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_table FOR EACH ROW
DECLARE
v_data my_table%ROWTYPE;
BEGIN
--assign variables
v_date.column1 := :new.column1;
v_date.column2 := :new.column2;
v_date.column3 := :new.column3;
...
...
v_date.column200 := :new.column200;

my_proc(v_data);
END;

Is there any smarter way to do this? I know I can generate the above code on SQL*Plus ...but I want the final code to be small and clean.

Thanks.

Edited by: Devang Thakkar on Feb 25, 2010 5:11 PM
Tagged:

Best Answer

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Answer ✓
    It would have been nice if Oracle had anything like what you ask. But here comes the bad news, Such a thing does not exist. You need to mention the column name in the OLD and NEW individually.

    Check this asktom link for further reference.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:482821541531#3384221019810

Answers

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    Answer ✓
    It would have been nice if Oracle had anything like what you ask. But here comes the bad news, Such a thing does not exist. You need to mention the column name in the OLD and NEW individually.

    Check this asktom link for further reference.

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:482821541531#3384221019810
This discussion has been closed.