Home » SQL & PL/SQL » SQL & PL/SQL » Populating 200 column values an existing row to a new row via trigger
Populating 200 column values an existing row to a new row via trigger [message #225812] Wed, 21 March 2007 10:18 Go to next message
milaan66
Messages: 12
Registered: March 2007
Junior Member
I have a table of 200 columns. I have created a template row with the values in all the columns. The application inserts a new row with values only in 4 columns (that are unique). Rest of the columns are blank.

What is the best way to populate rest of the column values from the template row upon insert?

THank you,
Milaan
Re: Populating 200 column values an existing row to a new row via trigger [message #225822 is a reply to message #225812] Wed, 21 March 2007 10:55 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Set the DEFAULT for each column you create, instead of trying to do it with a trigger.
SQL> create table default_test (col_1 varchar2(10), col_2 varchar2(10) default 'ABC');

Table created.

SQL> insert into default_test (col_1) values ('Col 1');

1 row created.

SQL> insert into default_test (col_1) values ('Col 2');

1 row created.

SQL> select * from default_test;

COL_1      COL_2
---------- ----------
Col 1      ABC
Col 2      ABC
Re: Populating 200 column values an existing row to a new row via trigger [message #226197 is a reply to message #225812] Thu, 22 March 2007 16:46 Go to previous message
tplank
Messages: 7
Registered: March 2007
Junior Member
Hopefully this will give you some ideas. There are a lot of things you can do to make this more efficient. I assumed you didn't want to have all of the columns listed in your program. You can place this in a package and have the template row only populate on package intialization. Let me know if you need help figuring this out.

declare
   mytab your_table%rowtype;
   template_id your_table.keycol_id%TYPE := 1;  -- unique key for template row
begin
   
   select * into mytab
     from your_table
    where keycol_id = template_id;
    
   mytab.COL1 := 1;
   mytab.COL2 := 2;
   mytab.COL3 := 3;
   mytab.COL4 := 4;
   
   insert into your_table
     values mytab;
end;

[Updated on: Thu, 22 March 2007 16:48]

Report message to a moderator

Previous Topic: Ways of optimizing codes
Next Topic: What triggers access a table?
Goto Forum:
  


Current Time: Sat Dec 10 09:17:01 CST 2016

Total time taken to generate the page: 0.22009 seconds