SQL trigger not firing after moving to new server
-
Hi all,
I can't seem to figure this out and its driving me bonkers!We recently moved our SQL database to a new SQL server. Both are 2008 R2.
Triggers I had put in place in the old SQL server are now not working in the new server. We have left the old server up temporarily because when it was taken down, our front end database (access 2007) produced errors eventually causing it become corrupted. The errors seem to be pointing to a link to the old server somewhere.
Because the old server is still up, the triggers are still occuring but they are coming from the old server. I can tell because the emails use the old profile name. But i can see all the new data being appended to the new server; and not to the old server.
I've tested sp_send_dbmail in the new server in a seperate query and it functions correctly
I am at a loss as to what to do next. I can stop the triggers firing from the old server by disabling them, but when i enable them in the new server, they don't work at all. I feel like there is a setting i need to change somewhere that i am missing?
Here is the code:USE [CMS]
GOSET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GOALTER TRIGGER [dbo].[EmailTriggerProjectsMissingReports2] ON [dbo].[tblProjectsMissingReports]
AFTER INSERT
ASBegin
Declare @PIID varchar (255)
Declare @Client varchar (255)Set @PIID = (Select ProjectiterationID from inserted)
Set @Client = (Select ClientName from inserted)DECLARE @msg varchar(500)
SET @msg = '' + @PIID + ', ' + @Client + ' is ready to be reported. Check the TBR for further information.'
--// CHANGE THE VALUE FOR @recipients
EXEC msdb.dbo.sp_send_dbmail @recipients=N'yadayada@yadayada.com; ', @body = @msg, @subject = 'Report Ready To Be Reported' , @Profile_Name = 'SQL Notifications'END
-
Triggers always fire. There's no setting (other than disabling it) to stop a trigger from firing.
Try setting up an extended event session to see exactly what's happening and what, if any, errors are thrown.p.s. That trigger won't work correctly if an insert ever puts more than one row into the table in a single statement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - GilaMonster - Sunday, August 13, 2017 5:15 PM
Thank you. I am learning as I go, and message boards like this are what show me the way! I looked in a few places for errors, but couldn't find them. But your message did make me realize that it wasn't a setting issue, so I went back and played with it a bit more and realized that I just had to delete all the linked tables in my front end database, and reimport them from the new server. Now the triggers are working fine. Lost a bit of data, as it was stored in the wrong database, but nothing important.
Thank you so much for your response. I was mentally stuck on the idea that it was a setting somewhere...understanding that it wasn't possible got me to move on and find the source of the problem!
- rhondamoeller - Sunday, August 13, 2017 8:18 PM
Ask the people that own the related database, tables, and applications if they agree with that. I certainly wouldn't.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply