July 1, 2003 at 7:23 am
We have an insert/update trigger that calls a sproc. Sproc needed adjusted and had odd name, so renamed and changed trigger to call new proc name.
Old trigger seems to be cached, indicator is that client side software attempting to update gives error that old proc name does not exist.
Dropped the triggers using enterprise admin, the old trigger still runs
Dropped the sproc, client software says sproc does not exist.
Readded sproc with old name, the old trigger runs fine!
Scripted the trigger and sproc, to force a drop and recreate, no impact.
SQL2000
Any comment appreciated. Tom
July 1, 2003 at 12:27 pm
Don't understand what's happening here, but here's a few randomly selected musings:
Have you tried updating the table from Query analyser rather than the app?
How about dropping the trigger and then looking in the system tables to check that it actually has gone?
(sysobjects where name = ? and xtype = 'TR')
You could try recreating the trigger with a different name, I suppose...
If it is a caching problem, DBCC FREEPROCCACHE should remove everything from the procedure cache - though I can't see why the trigger should be staying there. You will find a performance hit on first running of some stored procs if you do this, though.
As a last resort, you might have to drop and recreate the table: make a note of, then remove foreign keys to the table, as well as defaults, constraints and bound datatypes on the table, then copy data into a new table, drop old table, rename new table, add foreign keys, defaults, constraints and datatypes, re-enable foreign keys.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
July 28, 2003 at 6:13 am
STAX68, Your reply led me down the right path. It turns out I was calling the sproc from two different triggers. When I renamed the sproc and fixed one trigger the other trigger was still hanging out there.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply