Post reply

Can alter table via EM cause a disabled trigger to be enabled?

  • November 23, 2005 at 3:07 pm

    #110864

    Hi all,

    On the production server, I had to do an emergency change to a table and added a column.  I forgot this table had a >>disabled<< trigger in it.  Can the act of adding a column via EM cause a trigger to be enabled?

    Regards,

    Uday

  • November 24, 2005 at 12:21 am

    #605626

    which version of sqlserver is this ?

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 24, 2005 at 12:27 am

    #605629

    Hi there,

    I'm talking about SQL2K sp3a Ent Edition on W2k.

    Regards,

    Uday

  • November 24, 2005 at 1:01 am

    #605630

    Your finding is correct !!

    It does it wrong in SP3a _and_ SP4 !

    This should be reported to MS-PSS !

    This is the repro :

    set nocount on

    go

    -- drop table dbo.t_test

    go

    print 'tabel dropped'

    go

    CREATE TABLE dbo.T_Test (

     Sleutel int IDENTITY (1, 1) NOT NULL ,

     Ms_Ts timestamp NOT NULL ,

     Ms_Datetime datetime NOT NULL ,

     Ms_Datetime_Last_Used datetime NOT NULL ,

     Ms_Char char (10) NOT NULL

    ) 

    GO

    print 'Table reated'

    go

    ALTER TABLE dbo.T_Test WITH NOCHECK ADD

     CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,

     CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used,

     CONSTRAINT PK_T_Test PRIMARY KEY  NONCLUSTERED

     (

      Sleutel

    &nbsp   

    GO

    print 'Constraints added'

    go

    --drop trigger TrU_Ms_Datetime_Last_Used

    --go

    CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test

    FOR  UPDATE

    AS

    if not UPDATE(Ms_Datetime_Last_Used) 

     begin

     update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )

            end

    go

    print 'Trigger added'

     

    insert into t_test (Ms_Char) values('a')

    go

    insert into t_test (Ms_Char) values('b')

    go

    Print 'Rows inserted'

    go

    select * from t_test

    go

    Print 'Update starts here...'

    go

    update t_test set ms_char = 'c' where sleutel = 1

    go

    Print 'Sleutel 1 updated ...'

    go

    select * from t_test

    go

     

    select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime

      , CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime

     from t_test

    order by sleutel

    go

    update t_test set ms_char = 'D' where sleutel = 2

    go

    Print 'Sleutel 2 updated ...'

    go

    select * from t_test

    go

     

    select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime

      , CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime

     from t_test

    order by sleutel

    go

    ALTER TABLE t_test DISABLE TRIGGER  TrU_Ms_Datetime_Last_Used

    go

    WAITFOR DELAY '00:00:02'

    go

    update t_test set ms_char = 'y' where sleutel = 2

    go

    Print 'Sleutel 2 updated ...'

    go

    select * from t_test

    go

    --

    -- EM manage triggers shows the correct :

    -- -- -- -- --drop trigger TrU_Ms_Datetime_Last_Used

    -- -- -- -- --go

    -- -- -- --

    -- -- -- -- CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test

    -- -- -- -- FOR  UPDATE

    -- -- -- --

    -- -- -- -- AS

    -- -- -- --

    -- -- -- -- if not UPDATE(Ms_Datetime_Last_Used) 

    -- -- -- --  begin

    -- -- -- --

    -- -- -- --  update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )

    -- -- -- --

    -- -- -- --         end

    -- -- -- --

    -- -- -- --

    -- -- -- -- alter table [dbo].[T_Test] disable trigger [TrU_Ms_Datetime_Last_Used]

    -- Now use EM to add a column somewhere in between the other columns (so it cannot user alter table add column)

    --- This is what it generates (has the error !!! because it does not disable the trigger !!

    -- -- -- BEGIN TRANSACTION

    -- -- -- SET QUOTED_IDENTIFIER ON

    -- -- -- SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    -- -- -- SET ARITHABORT ON

    -- -- -- SET NUMERIC_ROUNDABORT OFF

    -- -- -- SET CONCAT_NULL_YIELDS_NULL ON

    -- -- -- SET ANSI_NULLS ON

    -- -- -- SET ANSI_PADDING ON

    -- -- -- SET ANSI_WARNINGS ON

    -- -- -- COMMIT

    -- -- -- BEGIN TRANSACTION

    -- -- -- ALTER TABLE dbo.T_Test

    -- -- --  DROP CONSTRAINT DF_T_Test_Ms_Datetime

    -- -- -- GO

    -- -- -- ALTER TABLE dbo.T_Test

    -- -- --  DROP CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used

    -- -- -- GO

    -- -- -- CREATE TABLE dbo.Tmp_T_Test

    -- -- --  (

    -- -- --  Sleutel int NOT NULL IDENTITY (1, 1),

    -- -- --  Ms_Ts timestamp NOT NULL,

    -- -- --  Ms_Datetime datetime NOT NULL,

    -- -- --  Ms_Datetime_Last_Used datetime NOT NULL,

    -- -- --  xxxx char(10) NULL,

    -- -- --  Ms_Char char(10) NOT NULL

    -- -- -- &nbsp  ON [PRIMARY]

    -- -- -- GO

    -- -- -- ALTER TABLE dbo.Tmp_T_Test ADD CONSTRAINT

    -- -- --  DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime

    -- -- -- GO

    -- -- -- ALTER TABLE dbo.Tmp_T_Test ADD CONSTRAINT

    -- -- --  DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used

    -- -- -- GO

    -- -- -- SET IDENTITY_INSERT dbo.Tmp_T_Test ON

    -- -- -- GO

    -- -- -- IF EXISTS(SELECT * FROM dbo.T_Test)

    -- -- --   EXEC('INSERT INTO dbo.Tmp_T_Test (Sleutel, Ms_Datetime, Ms_Datetime_Last_Used, Ms_Char)

    -- -- --   SELECT Sleutel, Ms_Datetime, Ms_Datetime_Last_Used, Ms_Char FROM dbo.T_Test TABLOCKX')

    -- -- -- GO

    -- -- -- SET IDENTITY_INSERT dbo.Tmp_T_Test OFF

    -- -- -- GO

    -- -- -- DROP TABLE dbo.T_Test

    -- -- -- GO

    -- -- -- EXECUTE sp_rename N'dbo.Tmp_T_Test', N'T_Test', 'OBJECT'

    -- -- -- GO

    -- -- -- ALTER TABLE dbo.T_Test ADD CONSTRAINT

    -- -- --  PK_T_Test PRIMARY KEY NONCLUSTERED

    -- -- --  (

    -- -- --  Sleutel

    -- -- -- &nbsp ON [PRIMARY]

    -- -- --

    -- -- -- GO

    -- -- -- --drop trigger TrU_Ms_Datetime_Last_Used

    -- -- -- --go

    -- -- --

    -- -- -- CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON dbo.T_Test

    -- -- -- FOR  UPDATE

    -- -- --

    -- -- -- AS

    -- -- --

    -- -- -- if not UPDATE(Ms_Datetime_Last_Used) 

    -- -- --  begin

    -- -- --

    -- -- --  update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )

    -- -- --

    -- -- --         end

    -- -- -- GO

    -- -- -- COMMIT

    ---

    -- -- -- then run these

    -- -- update t_test set ms_char = 'y' where sleutel = 2

    -- --

    -- -- go

    -- --

    -- -- Print 'Sleutel 2 updated ...'

    -- -- go

    -- --

    -- -- select * from t_test

    -- -- go

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 24, 2005 at 2:55 am

    #605650

    Hey

       Can u summerize what you have done and ur observations on it ? I am little confused

     

    Regards

    Shrikant   

    Regards
    Shrikant Kulkarni

  • November 24, 2005 at 3:28 am

    #605655

    In case you were addressing me :

    This testscript creates a table and puts an update trigger on it.

    The script was initialy for testing the ms_timestamp datatype vs. the datetime datatype.

    The part of "disable trigger" is new because of this forum thread.

    In enterprise manager (EM) you can design the table and add a new column somewhere, but not the last position for this test. and then have EM script it for you pushing the script_it_icon.

    In the generated script, you can see it does not add the "disable trigger" statement for the disabled trigger, whereas when you perform manage triggers it generates a script for the trigger, including a disable trigger statement.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • November 24, 2005 at 5:47 am

    #605678

    Thankx

    Regards
    Shrikant Kulkarni

  • November 24, 2005 at 9:34 am

    #605717

    Did you report this? If not I'll open a bug. Have you tried it on SQL Server 2005?

    Thanks

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • November 24, 2005 at 9:07 pm

    #605783

    Hi Paul,

    No, I have not yet reported to Microsoft, can you do it on my behalf please?

    Regards,

    Uday

  • November 24, 2005 at 9:12 pm

    #605785

    Paul,

    No, I have'nt tried this particular operation on Sql2005 yet.  I will and let you know if the same happens.

    Regards,

    Uday

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy