Home » SQL & PL/SQL » SQL & PL/SQL » How to create a table using Trigger
How to create a table using Trigger [message #416616] Mon, 03 August 2009 09:22 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12403
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 #416619 is a reply to message #416616] Mon, 03 August 2009 09:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
adusur wrote on Mon, 03 August 2009 16:22
Now, my requirement is that i need to create a table whaen ever
a row is inserted in to this table.


I really doubt if you need to do this, but I am quite certain that you do not want to do this.
You think you are having a problem now? Get used to it, because from now on you will write nothing but dynamic SQL. Tables created at runtime can ONLY be accessed using dynamic SQL.

Either rethink your design or give up your hobbies Wink
Re: How to create a table using Trigger [message #416630 is a reply to message #416616] Mon, 03 August 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From one of your previous posts:
Michel Cadot wrote on Tue, 21 July 2009 09:13
...
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: How to create a table using Trigger [message #416880 is a reply to message #416630] Wed, 05 August 2009 00:05 Go to previous message
Kevin Meade
Messages: 2101
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
Previous Topic: time consuming query
Next Topic: chat
Goto Forum:
  


Current Time: Fri Dec 02 12:26:07 CST 2016

Total time taken to generate the page: 0.10090 seconds