[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

I have an UPDATE trigger in SQL Server 2008 which constantly disappears

Posted on 2011-10-27
4
Medium Priority
?
318 Views
Last Modified: 2012-05-12
Hi,

I have the same application running on 2x platforms.  Both systems have a dedicated DB server, one runs SQL Server 2005, the other 2008 (R2)

On both dbs I have a table and on that table there should be 2x triggers, one managing inserts, the other updates.

On the 2005 server, I have no issues.

On the 2008 server, the insert trigger is fine but the update trigger keeps disappearing / vanishing without any explanation.  Literally, I create the trigger from a stored script, refresh the triggers branch and I can see it.  I'll come back 10-15 mins later and refresh again - I can see the insert trigger but the update trigger has gone.  I haven't dropped the trigger nor the table nor do I believe anyone else could or would be taking actions which conflicted with my work (e.g. colleagues working on the same db instance).

The triggers aren't complex - script is attached.

Confused :-(

Thanks in advance.  Changes-to-IssueHeader-Triggers-.sql
0
Comment
Question by:LogistixSW
  • 2
4 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37037291
either someone drops/recreates the table, or indeed drops the trigger.

check out this to "see/trace" the event: ddl triggers
http://www.sql-server-performance.com/2007/ddl-triggers/

now, if that trigger also disappears, then you have someone that either really drops all triggers in the database, or even fully "restores" the db.
if it's a database restore, you can see that in the sql server ERRORLOG file
otherwise, use the SQL Profiler tool to trace all sql, and find the culprit there.
0
 

Accepted Solution

by:
LogistixSW earned 0 total points
ID: 37037355
DOH!!!!

I had failed to put a GO statement in my script after creating the INSERT trigger.  Thus, if you scripted the INSERT trigger back out, it was actually:

<<
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[TG_IssueHeader_INSERT]'))
EXEC dbo.sp_executesql @statement = N'
create trigger [dbo].[TG_IssueHeader_INSERT] on [dbo].[IssueHeader]
for insert
as
begin

      DECLARE
            @WhseRef NVARCHAR(20)

      SELECT @WhseRef = IssueHdr_WhseRef
      FROM Inserted

      if(@WhseRef is null or Len(@WhseRef) < 1)
      BEGIN

            update IssueHeader
            set
                  IssueHdr_WhseRef = cast(Inserted.IssueHdr_ID as varchar(20))
            from Inserted
            where IssueHeader.IssueHdr_ID = Inserted.IssueHdr_ID
      END
end

---

IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''[dbo].[TG_IssueHeader_UPDATE]''))
DROP TRIGGER [dbo].[TG_IssueHeader_UPDATE]'
GO
>>

so every time a record was inserted, it dropped the UPDATE trigger.

Muppett!
0
 

Author Closing Comment

by:LogistixSW
ID: 37061195
My own dumbass fault :(
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 37039870
ok glad you found that ...

however ,,,, of longer term importance...

please write your triggers correctly ...

1) to handle multiple row situations
2) to just reference the changed set of data..

e.g. write it more like

create trigger [dbo].[TG_IssueHeader_INSERT] on [dbo].[IssueHeader]
for insert
as
begin
            update IssueHeader
            set 
                  IssueHdr_WhseRef = cast(I.IssueHdr_ID as varchar(20))
            from issueheader as h
            inner join Inserted as I
              on h.IssueHdr_ID =i.IssueHdr_ID 

            where len(coalesce(h.issuehdt_whseref,'')<1
    
end

Open in new window

0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question