Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-06-04 : 08:11:47
|
| Nathan writes "After restoring a database some of my triggers no longer fire.I am using SQL Server 2000 on windows 2000. I have a model database with some initial data that I copy for new clients by creating an empty database and restoring the model into that database, and it works like a charm. But for some reason not all of my triggers are firing. I have a trigger set up on a database table that calls a stored procedure that calls sp_UOCreate and sp_OAMethod to timestamp a text file I am using for an ASP.NET cache dependency. My last two database copies, however, are not firing that trigger when the database table is updated. If I call the stored procedure manually, i.e. exec usp_TimeStampCacheFile, it works like a charm. I've deleted and re-created the trigger in the new database and the trigger appears in the trigger folder of the table. Scripting the trigger to a window displays the correct code. Still, no response. I'm starting to get a little deseperate :) I have no idea what could cause a trigger not to fire. Thanks in advance,Nathan" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-04 : 08:58:00
|
| your database isn't actually called 'model' is it?have you tried running a trace in SQL Profiler to see what's going on?<O> |
 |
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-06-04 : 12:22:33
|
| Could it be a recursive Trigger? If so, check the OPTIONS tab on your database PROPERTIES and make sure the option is still selected. You may consider toggling it just to be sure (Set it off, then set it on again).If I understand correctly, some of the triggers work fine? You may also try re-enabling the trigger ALTER TABLE ENABLE TRIGGER ALL -- although recreating a trigger should have enabled it by default.Also, check your NESTED trigger settings. To set nested triggers on-sp_configure 'nested triggers', 1For more ideas on triggers, see:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_6nw3.aspIt is also strange that you made no mention of error messages. Do these trigger operate solely in the restored database, or do they access other database tables as well?Edited by - kevin snow on 06/04/2002 12:34:03 |
|
|
|
JamesH
Posting Yak Master
149 Posts |
Posted - 2002-06-04 : 12:52:09
|
| Have you tried executing sp_recompile on the tables in question?JamesH. |
|
|
|
PlasticLizard
Starting Member
7 Posts |
Posted - 2002-07-02 : 16:54:38
|
| Ok, here is some more information, but I am thinking maybe there is something wrong with my SQLServer installation. I haven't been back to update this topic until now because miracles of miracles subsequent databases I created using the method I use (restoring a model) were working fine, I set up two or three more databases in which the triggers worked like a champ. Now however it seems to have stopped working entirely. Running a trace in SQL Profiler shows only that the stored procedures inside the triggers aren't being executed, meaning I presume the trigger isn't firing. The trace in a working database shows those procedures being executed. Enabling triggers, recompiling the table, and various other similar methods don't do anything.There are no errors being generated (at least that I know of), the triggers just don't fire, plain and simple. I thought maybe the model database was corrupted but restoring from another working database doesn't work either (although granted they were restored originally from the model). Any other ideas? Given the intermittent nature of the problem, that it works (or worked) sometimes and not others, leads me to believe something is corrupted, but what? I'm completely at a loss on this one.Thanks in advance, and for the previous ideas,Nathan |
|
|
|
VyasKN
SQL Server MVP & SQLTeam MVY
313 Posts |
Posted - 2002-07-02 : 17:09:05
|
| Could it be that the triggers are disabled for some reason?You could run the following query in your database to see if there are any triggers that are disabled:SELECT name AS [Diabled trigger], OBJECT_NAME(parent_obj) AS [Table Name] FROM sysobjects WHERE OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 1 AND type = 'TR'GOIf there are any disabled triggers, then run the following for each trigger to re-enable them:ALTER TABLE TableNameHere ENABLE TRIGGER TriggerNameHereGOAnother possibility. You say, sometimes it works and sometimes not. Are you doing any kind of bulk copy (for example: BCP, BULK INSERT). By default, those bulk operations won't fire triggers, unless you ask them to.--HTH,Vyashttp://vyaskn.tripod.com |
|
|
|
|
|
|
|
|
Triggers not firing after restore