By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,448 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Create a table using triggers

P: 2
Hi,

I have a table called A (say) with columns called name, place, animal and thing.
I would want to write an on insert trigger on this table, which would create a table with the name of the value entered in the name column.

Let's say a new column is entered with the value of name column as mickey. I want to create a table called mickey, with the help of triggers.

I'm really new to this and i would like some help. Thanks in advance for this help!!

I know the syntax..which may go like this

CREATE TRIGGER <trigger_name>
AFTER INSERT ON INST1.A
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
CREATE TABLE INST1.NEW.NAME
--Name is a column in the table
(
ID LONG NOT NULL PRIMARY_KEY,
);

Is this a right syntax and can i use AND or something to write another CREATE TABLE statement below. Like, say i have to create another table with the value under place column, can i do this?

CREATE TRIGGER <trigger_name>
AFTER INSERT ON INST1.A
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
CREATE TABLE INST1.NEW.NAME
--Name is a column in the table
(
ID LONG NOT NULL PRIMARY_KEY,
)
AND
CREATE TABLE INST1.NEW.PLACE
--Place is a column in the table
(
ID LONG NOT NULL PRIMARY_KEY,
);

Or do i have to write another trigger? Can i have more than one after insert triggers on the same table with different names to the triggers?

Thanks in advance to all those who help...

Thanks once again,
Lakuma
May 16 '07 #1
Share this Question
Share on Google+
2 Replies


frozenmist
Expert 100+
P: 179
Hi lakuma,
See what you have to have is a dynamic SQL to create a table.
So You can have a procedure that executes the dynamic sql to create a table with name as that of a value that has been inserted. then you can call the procedure from the trigger.
I will give you a sample
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE SAMPLE_TABLE
  2. (
  3. NAME  VARCHAR(30),
  4. PLACE VARCHAR(30),
  5. ANIMAL VARCHAR(30),
  6. THING VARCHAR(30)
  7. )
  8.  
IS YOUR TABLE.

THIS IS THE PROCEDURE YOU CAN WRITE(creates a table with one column)
Expand|Select|Wrap|Line Numbers
  1. create procedure sample(IN NAME VARCHAR(100))
  2. language sql
  3. begin
  4. DECLARE V_SQL VARCHAR(500);
  5. SET V_SQL = 'CREATE TABLE '||NAME||' (COL1 INTEGER) ';
  6. EXECUTE IMMEDIATE V_SQL;
  7. end
  8.  
Finally, this can be your trigger

Expand|Select|Wrap|Line Numbers
  1. CREATE TRIGGER CREATE_TR 
  2. AFTER INSERT ON SAMPLE_TABLE
  3. REFERENCING NEW AS N
  4. FOR EACH ROW
  5. BEGIN ATOMIC
  6. CALL SAMPLE(N.NAME);
  7. END
  8.  
  9.  
Try something of this sort out on the table you have.
Hope that helped
Cheers
May 17 '07 #2

P: 2
Thank you so much...that helped a lot!!!
May 18 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.