Is it possible to do this in MYSQL

[lampstax]

Is it possible to create something in MySQL ( trigger ? stored procedure ? something else ? ) so that when any table is created on a DB thats has a name fitting a certain structure .. lets say like "user1_data", "user2_data" .. to insert a row into that newly created table?

 

Ofcourse this structure is a pretty ugly db design ( not mine ) and it could be done easily a php / java / ruby script, the situation dictates that no script is used.

 

I'm stumped.  What about you guys?

[lampstax]

Actually if I can get this trigger to work, it would solve my problems too, but I'm have some syntax issues

 

DELIMITER |

CREATE TRIGGER wpmu_new_blog_set_default_options AFTER INSERT ON wp_blogs
FOR EACH ROW BEGIN
SELECT SLEEP(2);                                                                                                                                 
SET @var := CONCAT('wp_', NEW.blog_id ,'_options');
UPDATE @var SET option_value = '0' WHERE option_name = 'option_x'; 

END;
|

DELIMITER ;

 

I feel like I'm sooo close, but its not working.  I"m not too familiar with triggers.  I bet its something small that I'm not seeing.

 

The MySQL error that I see is :

 

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near '@var
SET option_value = '0' WHERE option_name = 'option_x';

[gizmola]

You've hit a dead end.  MySQL won't allow you to inject dynamic SQL statements into triggers or stored procedures.

[gizmola]

Well it looks like this might be doable using a prepared statement, although i'm still not sure it would work in a trigger.  Give it a try.

[lampstax]

Tried it too ways:

 

DELIMITER |

CREATE TRIGGER set_default_options AFTER INSERT ON authors
FOR EACH ROW BEGIN
SELECT SLEEP(2);  
PREPARE stmt_name FROM "UPDATE authors SET issued = '9' WHERE authorID = ?";
SET @test_parm = "2";
EXECUTE stmt_name USING @test_parm;
END;
|

DELIMITER ;

 

and even this didnt work

 

DELIMITER |

CREATE TRIGGER set_default_options AFTER INSERT ON authors
FOR EACH ROW BEGIN
SELECT SLEEP(2);                                                                                                                                 
PREPARE stmt_name FROM "UPDATE authors SET issued = '9' WHERE authorID = '2'";
EXECUTE stmt_name;
END;
|

DELIMITER ;