February 25, 2011 at 8:50 am
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.
February 25, 2011 at 8:58 am
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
February 25, 2011 at 9:22 am
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.
February 28, 2011 at 3:35 pm
Could it be that this is happening because the version of SQL Server is 2005 Express?
February 28, 2011 at 3:42 pm
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
February 28, 2011 at 3:58 pm
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!
😛
March 1, 2011 at 10:11 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply