Solved

SQL Server Trigger Dissapears

Posted on 2014-04-16
14
178 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
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.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
query question 12 32
SQL Query Syntax to add subtotal calculation by Contractor for each row. 3 24
t-sql need help on t-sql 10 25
SQL Query 2 31
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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 ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

808 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