Create a table using triggers

[lakuma]

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

[frozenmist]

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

CREATE TABLE SAMPLE_TABLE
(
NAME  VARCHAR(30),
PLACE VARCHAR(30),
ANIMAL VARCHAR(30),
THING VARCHAR(30)
)
 

IS YOUR TABLE.

THIS IS THE PROCEDURE YOU CAN WRITE(creates a table with one column)

create procedure sample(IN NAME VARCHAR(100))
language sql
begin
DECLARE V_SQL VARCHAR(500);
SET V_SQL = 'CREATE TABLE '||NAME||' (COL1 INTEGER) ';
EXECUTE IMMEDIATE V_SQL;
end
 

Finally, this can be your trigger

CREATE TRIGGER CREATE_TR 
AFTER INSERT ON SAMPLE_TABLE
REFERENCING NEW AS N
FOR EACH ROW
BEGIN ATOMIC
CALL SAMPLE(N.NAME);
END
 
 

Try something of this sort out on the table you have.
Hope that helped
Cheers

[lakuma]

Thank you so much...that helped a lot!!!