Solved

SQL Server Trigger Dissapears

Posted on 2014-04-16
14
174 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server tables join on parse list 6 22
Loop to go backward 90 days 2 18
Find results from sql within a time span 11 29
SQL view 2 26
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

786 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