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

  • 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

  • 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

  • Hi there,

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

    Regards,

    Uday

  • 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

  • Hey

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

     

    Regards

    Shrikant   

    Regards
    Shrikant Kulkarni

  • 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

  • Thankx

    Regards
    Shrikant Kulkarni

  • 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

  • Hi Paul,

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

    Regards,

    Uday

  • 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 9 (of 9 total)

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