?
Solved

SQL Server Trigger Dissapears

Posted on 2014-04-16
14
Medium Priority
?
190 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 

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
 
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:Scott Pletcher
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:Scott Pletcher
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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

764 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