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

Question

Post reply

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

uday

SSChasing Mays

Points: 633
More actions
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

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

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

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 1

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

uday SSChasing Mays Points: 633 More actions · Answer 2

Hi there,

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

Regards,

Uday

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 3

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

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 4

Hey

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

Regards

Shrikant

Regards
Shrikant Kulkarni

Johan Bijnens SSC Guru Points: 135258 More actions · Answer 5

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

shrikant-279400 SSCommitted Points: 1651 More actions · Answer 6

shrikant-279400

SSCommitted

Points: 1651

November 24, 2005 at 5:47 am

#605678

Thankx

Regards
Shrikant Kulkarni

Paul Randal One Orange Chip Points: 29544 More actions · Answer 7

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

uday SSChasing Mays Points: 633 More actions · Answer 8

Hi Paul,

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

Regards,

Uday

uday SSChasing Mays Points: 633 More actions · Answer 9

Paul,

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

Regards,

Uday