Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Question Unanswered: 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.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    self modifying code can be a lot of fun to debug, don't you think?

  4. #4
    Join Date
    Aug 2006
    Posts
    58
    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.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    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.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I wonder if you can pop a messagebox from a trigger in oracle? like "I am about to modify your schema. Confirm: yes/no"

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by jp7234
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •