Solved

SQL Server Trigger Dissapears

Posted on 2014-04-16
14
170 Views
Last Modified: 2014-10-04
Greetings!

I have an ongoing issue where an insert trigger dissapears from a DB after its first execution. The trigger is supposed to calculate some information and the insert a record into a separate table. Currently, the trigger fires, calculates the data, inserts the record into the appropriate table, and then dissapears completely from the DB.

We've never had this issue in any other DB and we have the trigger installed in countless other sites; so it seems to be an environmental thing.

Does anybody know of a situtation where a trigger would be dropped automatically by SQL Server due to an issue during execution or anything else?

Very broad question, but I'm running out of things to try.

Thanks!
0
Comment
Question by:PlawCoder
  • 7
  • 3
  • 2
  • +2
14 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40004939
Has somebody accidentally left an DROP statement in the CREATE TRIGGER script, or something along those lines?
0
 

Author Comment

by:PlawCoder
ID: 40004949
Thanks prodigy, but that's one of the first things that I checked, since I made that mistake a long time ago with an SP. Wish that was it. :/
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40004951
Do you have any other triggers on the same table? Or any cascading triggers?
0
 

Author Comment

by:PlawCoder
ID: 40004964
There is one other trigger on the table, but it is a DELETE trigger which seems to be working fine. There is no DROP trigger on that one either.

No, there is no cascading trigger. The table where the INSERT trigger sends the information has no trigger at all.
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40004981
Well, as i'm sure you know, triggers don't just disappear for no reason :)

It might be worth looking through the default trace to see if it provides any information on what dropped the trigger. Or, if you can, run a profiler trace and trigger the trigger and see what activity you get.
0
 

Author Comment

by:PlawCoder
ID: 40004995
Yes, I hope I've learned at least that much in quite a few years of work. LOL :)

I thought at first that some issue with the data was causing the trigger to fail which would in turn cause SQL Server to drop it, but since I can find no such scenario anywhere online, then it must be some other confounding issue which I can't pinpoint yet.

I do like your suggestion, so I'll see if we can run the profiler on that client's machine.

Thanks for the tip...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40005623
Just do a search through all the objects in the database for the trigger name (SQL Search is very useful for this).  Unless the DROP TRIGGER is (God forbid) called from the client, you should find it in your database somewhere.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40006780
>>" insert trigger disappears from a DB after its first execution"
Have you tested to see if its actually executing the trigger that drops it or perhaps something else?

One thing which is worth checking is to find out if there's an scheduled job or package (e.g. export/import DTS, replication, etc.) that might be dropping and recreating the whole table as part of an ETL process.
0
 

Author Comment

by:PlawCoder
ID: 40006791
Anthony:

I'll go ahead and do a search just in case. We're currently trying to trace with the Profiler to see if we find anything worthwhile.

ProjectChampion:

We discussed that upon encountering the issue, but the problem is easily reproduced whenever an insert happens on the table; not necessarily on a specific time of the day.

Thank you both very much for the suggestions and ideas. ¿
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40007151
Does the trigger actually disappear/get dropped, or might it just be disabled?  I can see SQL possibly automatically disabling a trigger.
0
 

Author Comment

by:PlawCoder
ID: 40007155
It goes entirely away. You can no longer see it available in the object browser.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40007217
Review the text of the trigger immediately after it's created just to be sure:


SELECT OBJECT_DEFINITION ( OBJECT_ID('<trigger_name>') )
0
 

Accepted Solution

by:
PlawCoder earned 0 total points
ID: 40350986
Thank you all for your help. The problem, annoyingly enough, was a GO statement in the wrong spot.
0
 

Author Closing Comment

by:PlawCoder
ID: 40361030
Problem related to incorrect script structure.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

16 Experts available now in Live!

Get 1:1 Help Now