Post reply

ALTER DATABASE statement not allowed within multi-statement transaction.

  • November 4, 2009 at 5:15 am

    #142877

    I have a tigger for insert statement in one of my table.

    what the trigger to do is when new row is inserted in the table it calls a procedure

    which alters a database and creates a new file group on it.

    but it through error when u insert row in table

    as MSG 226 'ALTER DATABASE statement not allowed within multi-statement transaction.'.

    or any anther solution in which any row is insered in atable then it fires a code which has alter database command.

  • November 4, 2009 at 8:03 am

    #1074822

    don't do this in a trigger.

    do it in a schedule job that occurs every X days, which would determine if the filegroup needs to be created yet or not....and if it does, move the appropriate data to the new file group in that job.

    the creation of the file group does not have to be instantaneous just because you inserted data...it can wait for regularly schedule maintenance. You want to avoid doing DBA type jobs in a trigger, and only do data realted changes in triggers instead.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 4, 2009 at 8:31 am

    #1074841

    Alter Database requires exclusive database access. To make this work, you'd have to make the code disconnect all other connections to the database and kill all pending transactions. Is that really what you want?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • November 5, 2009 at 3:57 am

    #1075232

    mostly i want to create a dynamic partition on basis of new value insert into a partitcular table

    when a new value is inserted into a table then trigger fires and it adds filegroup and new range of partition in partition function.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply