• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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

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
LogistixSW
Asked:
LogistixSW
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
LogistixSWAuthor Commented:
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
 
LogistixSWAuthor Commented:
My own dumbass fault :(
0
 
LowfatspreadCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now