| How to create a table using Trigger [message #416616] |
Mon, 03 August 2009 09:22  |
adusur
Messages: 36
Registered: June 2009
Location: Bhubaneswar
|
Member |
|
|
Hi,
I have a table named trg_samp.
The structure of this table is
Name Null? Type
----------------------------------------------------------------------------------------------- ---
GLOBALNAME VARCHAR2(50)
NODENAME VARCHAR2(30)
COLUMNS NUMBER(10)
Now, my requirement is that i need to create a table whaen ever
a row is inserted in to this table.
The table name must be the concatenated value of GLOBALNAME and NODENAME.
And the number of columns in newly created should be equal to
columns value of the trg_samp table,
and column names will be col1,col2,col3...coln.
I have written the following trigger to do this, but this trigger
is not getting fired after i insert a row in to trg_samp table.
create or replace trigger createtab_trg
after insert on trg_samp
for each row
declare
-- local variables here
tabname varchar2(50);
tab1 varchar2(50);
tab2 varchar2(50);
cols number(10);
cursor cur_tab is select * from trg_samp;
begin
open cur_tab;
loop
Fetch cur_tab into tab1,tab2,cols;
exit when cur_tab%notfound;
select GLOBALNAME into tab1,NODENAME into tab2,COLUMNS into cols from scott.glonode;
tabname:= tab1||tab2;
execute immediate 'create table ' || tabname || '(col1 varchar2(50))';
--execute immediate 'create table rav1(col1 varchar2(50))';
commit;
for i in 2..cols
loop
execute immediate 'alter table '||tabname||' add (col'||i||' varchar2(50))';
--execute immediate 'alter table rav1 add (col2 varchar2(50))';
end loop;
end loop;
commit;
close cur_tab;
commit;
exception
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end createtab_trg;
Please let me know why the trigger is not working.
Thanks in Advance,
Ravi.
|
|
|
|
| Re: How to create a table using Trigger [message #416618 is a reply to message #416616] |
Mon, 03 August 2009 09:28  |
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
It's firing.
It's also erroring.
Remove the exception handler.
If I was you I'd rethink using a trigger for this - it's going to be a lot harder than you think.
In fact I'd rethink creating tables on the fly at all. I don't know why you think you need to but it's almost always a bad idea.
And next time you post code can you please use code tags.
|
|
|
|
|
|
|
|
| Re: How to create a table using Trigger [message #416880 is a reply to message #416630] |
Wed, 05 August 2009 00:05 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
my guess is java developer or .net developers want it. I have seen this several times in the past. It is not a database guys design for a solution, it is a developers design for a solution.
There may some day be someone who throws out a legitimate reason why this should be done.
In any event, it is not legal to do a commit in a trigger. Your trigger will fail.
OK so if you really want to do it you need to use an autonomous_transaction.
I don't know for sure, as I have not seen what you are trying to achieve, but if what you are doing is like everyone else, this is a bad idea as was already stated.
Good luck, you are going to need it. Here is what will happen. Things will go fine for six months, maybe a year, then something will go wrong. Last few times I saw this, it was performance issues with no way to fix them. Of course the geniuses who created the solution were no where to be found. They had all moved on to other projects leaving people with no clue what was going on to fix it. If you do this, make sure you document it well. That way when it goes south, at least they will know who to blame.
We all suggest you ask your architects to rethink the design.
Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
