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

Posted on 2011-10-27
Last Modified: 2012-05-12

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
Question by:LogistixSW
    LVL 142

    Expert Comment

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

    check out this to "see/trace" the event: 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.

    Accepted Solution


    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

                @WhseRef NVARCHAR(20)

          SELECT @WhseRef = IssueHdr_WhseRef
          FROM Inserted

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

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


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

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


    Author Closing Comment

    My own dumbass fault :(
    LVL 50

    Expert Comment

    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
                update IssueHeader
                      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

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
    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.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    761 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now