How to Alter a table in a trigger?

Hi,everyone.

I have a problem with SQL SERVER 2005, described as follows,

(1) To create two tables MASTERINFO and PRODUCT,

--TABLE MASTERINFO--
CREATE TABLE MASTERINFO
(
ID CHAR(2),
FIELDNAME VARCHAR(50),
FIELDTYPE VARCHAR(50),
);

Table MASTERINFO with following records,

MASTERINFO('1','PRODUCTNAME','VARCHAR(50)');
MASTERINFO('2','PRODUCTADD','VARCHAR(50)');
MASTERINFO('3','PRODUCTEXP','VARCHAR(50)');

--TABLE PRODUCT--
CREATE TABLE PRODUCT
(
ID CHAR(5),
PRODUCTNAME VARCHAR(50),
PRODUCTADD VARCHAR(50),
PRODUCTEXP VARCHAR(50),
);

In our project, field name and field data type of fields PRODUCTNAME,PRODUCTADD and PRODUCTEXP in the table PRODUCT are changed with values of fields FIELDNAME and FIELDTYPE in the table MASTERINFO.That is to say, when using the following UPDATE statement,

UPDATE MASTERINFO SET FIELDNAME='PRODUCTNAME1',FIELDTYPE='VARCHAR(60)' WHEN ID='1';

We hope that field name of field PRODUCTNAME is automatically changed into PRODUCTNAME1 and data type of its is changed into 'VARCHAR(60) in the table PRODUCT.

I want to use a trigger to realize, but I failed for ALTER TABLE statement can not be included in the trigger. How can I do?

Please give me some advice. Thank you in advance.The mere fact that you feel the need to alter your table structure in a trigger demonstrates that you need to rethink your entire database design.

I'm not kidding...

Describe the database application you are trying to build, and maybe we can give you some tips.|||self modifying code can be a lot of fun to debug, don't you think?|||Think you!

In oracle DBMS, we can use EXECUTE IMMEDIATE statement to finsih such a task.However, I can not find the right statement in SQL SERVER 2005.|||who cares how to do it or if you can do it in Oracle? dude, the whole point is if it is a good idea or not and any code that modifies the definition of permanent database objects as part of the application is a terribly bad idea. I believe there is a way to do this in sql server, but I am not going to hand you a loaded gun to shoot yourself with.|||I wonder if you can pop a messagebox from a trigger in oracle? like "I am about to modify your schema. Confirm: yes/no"|||You can't do it through a trigger.

You could conceivably do it through a stored procedure, and under a good database design all interaction with tables would be done via sprocs anyway. But then again, we already know we're not talking about a "best practices" database design here.|||Hi,everyone.
Please give me some advice. Thank you in advance.

Sure, no problem.

Don't do it.

Now, for the fun part.

Why do you want to do this?