Post reply

transaction question

  • June 5, 2006 at 11:29 am

    #60959

    Can alter table statements be part of a transaction? For example:

    begin transaction

    Alter table disable constraint

    Insert records

    alter table enable constraint

    end transaction

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • June 6, 2006 at 6:36 am

    #641938

    in a nutshell, yes you can, but by disabling constraints, you can insert data that does not adhere to the constraint definitions:

    create table test

    (testid int identity not null primary key,

     sysid int,

     testcol char(1) default 'Y' check (testcol='Y' or testcol='N' ),

    foreign key (sysid) references sysobjects(id)  )

    begin transaction

    ALTER TABLE test NOCHECK CONSTRAINT ALL

    insert into #test(sysid,testcol) values (-1,'W')

    ALTER TABLE test CHECK CONSTRAINT ALL

    commit transaction

    results:

    testsysidtestcol
    1-1W

    i do not have an id in sysobjects with a negative value, and the 'W' i inserted does not conform witht he check constraint. so yes you can do it, just be sure you do not have un-intended consequences.

    in

    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!

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

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