[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Server Trigger Dissapears

Posted on 2014-04-16
14
Medium Priority
?
197 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 70

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 70

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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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