Disappearing DML Triggers

Question

Disappearing DML Triggers

SQL_ME_RICH

SSChampion

Points: 11200
More actions
February 25, 2011 at 8:50 am

#232657

OK - please don't bash me too hard for this one, but I have a situation that has been brought to me by a colleague in the field (who is at the customer site as I type), and is claiming that his DML triggers are disappearing on him. Now - I know this can't just happen like that, but I am trying to make sense of the potential for this having happened to anyone else.
Has anyone had issues with DML triggers just p and disappearing on you before? This is a SQL Server 2005 SP3 environment. The code for the trigger is as follows...
/****** Object: Trigger [Trg_AlertLogCommand] Script Date: 02/21/2011 12:12:12 ******/
IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[Trg_AlertLogCommand]'))
DROP TRIGGER [dbo].[Trg_AlertLogCommand]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--Create Trigger
CREATE TRIGGER [dbo].[Trg_AlertLogCommand]
ON [dbo].[AlertLog]
AFTER INSERT
AS
BEGIN
--Creates new Command table record when a row instert takes place on the AlertLog table. ~RWY 2/14/2011
INSERT INTO dbo.Command ([Requested],[TableID],[RecordID],[Command],[RetriesAttempted],[Cancelled])
SELECT DTRaised, '1', CAST(AlertID AS VARCHAR(3)) + '-' + CAST(TreaterID AS VARCHAR(5)) + '-' + CONVERT(VARCHAR, DTRaised, 101) + ' ' + CONVERT(VARCHAR, DTRaised, 114), '0', '0', 'False'
FROM inserted
WHERE DTRaised IS NOT NULL
END
The trigger compiles fine, and winds up in the table it should, but then it is just disappearing. I am having my colleague do his thing to replicate the issue he experienced when this happened, but at the moment - this is all I have to go on.
Many thanks for all and any input on this.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

the drop of a trigger would be in the default trace, so you might be able to narrow down if it's a person or a process based on teh hostname, application name, and log in the trace;

you have to hurry though, the trace rolls over and overwrites if there is a lot of DDL activity.

easiest way to check is in the SSMS Gui:

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!

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 2

Lowell, thank you for the starting place. He has a few CREATES after a DROP, but it seems like one or more DROPS are missing. Then again - he may have just recreated multiple times. Hard to tell, but nonetheless - thank you for this report info.

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 3

Could it be that this is happening because the version of SQL Server is 2005 Express?

Lowell SSC Guru Points: 323518 More actions · Answer 4

Rich Yarger (2/28/2011)
Could it be that this is happening because the version of SQL Server is 2005 Express?

nope...something is specifically occurring...there's got to be something that is issuing the commands to drop it explicitly; SQL wouldn't/doesn't drop objects all on it's own.

I figured if you could identify the machine and login from the trace, you could track down from there if there was a script or developer or some process that is dropping your objects.

if you find the offending login, maybe you could take away DDL rights from that login, and see if a script or user complains about it?

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!

SQL_ME_RICH SSChampion Points: 11200 More actions · Answer 5

Found it! Major rookie mistake - I forgot to issue the GO after the END of my trigger. Guess what it was doing? Including some of the lines below for the next Trigger to be created in my patch/main creation script(s), which happened to be the IF EXISTS then DROP lines before CREATING a new object (in this case - a DML Trigger).

It finally dawned on my manager and I as we were talking about it. I noticed this earlier today, and only now put it together. Dumb dee dumb dumb.

Thanks Lowell!

😛

tfifield SSCrazy Eights Points: 9655 More actions · Answer 6

Don't feel bad Rich. I once created a utility to concatenate script files into 1 big script and forgot to make sure there was at 1 GO statement between the files. Major Oooops.

Todd Fifield