November 23, 2005 at 3:07 pm
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
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
Hi there,
I'm talking about SQL2K sp3a Ent Edition on W2k.
Regards,
Uday
November 24, 2005 at 1:01 am
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
 
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
-- -- --   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
-- -- --   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
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
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
Thankx
Regards
Shrikant Kulkarni
November 24, 2005 at 9:34 am
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
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
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