Problem Trigger

Question

Problem Trigger

xnfec

SSC Veteran

Points: 271
More actions
November 18, 2004 at 4:49 am

#165456

I have a couple of triggers on a table - one an insert and the other an update. It works fine, but I need to drop it and I cant. If I try to edit it, the whole database becomes unusable - hence my attempts to drop it. I tried dropping it using OSQL overnight, but still the same problem.
I've trawled the Internet for similar problems, but to no avail.
Any help/Comment appreciated. I can post the SQL if needed.

BillC

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Are you trying to drop it via DROP TRIGGER? Maybe you should post your statement.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

xnfec SSC Veteran Points: 271 More actions · Answer 2

Frank

this is the script that I was trying to run last night:

use MaintSQL

go

drop Trigger dbo.NewTblProperty_TriggerUpdateSurveyor

go

drop Trigger dbo.NewTblProperty_UpdateSurveyor

go

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

And what was the error message?

How did you try to run this?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

xnfec SSC Veteran Points: 271 More actions · Answer 4

I ran it from a batchfile I keep for these purposes. It runs nightly. The command line I used was:

osql -E -S Orinoco -i DropTriggersOnNewTblProperty.sql > 17112004_2.txt

there was no error message, just the DOS window had not closed signifying that the process had either hung or not terminated.

I did try to run the SQL in Analyser, but it just takes for ever and stops users from working.

Bill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

So, as this sounds strange and as a kind of last resort, did you also try this from within Enterprise Manager? "Usually" this operations should only be a matter of moments.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

xnfec SSC Veteran Points: 271 More actions · Answer 6

I usually do this in an ADP Access database, but yes, when that failed I tried it in Enterprise manager - hence the saga of trying to drop it via SQL

Bill

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

...but you are logged in with appropriate privileges, right?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

This is probably not more than guessing, so bear with me

- you are in the right db?

- the triggers exists?

- they are not recreated by any other operation?

Hey, I'm running out of ideas.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

xnfec SSC Veteran Points: 271 More actions · Answer 9

The triggers are :

NewTblProperty_TriggerUpdateSurveyor

and

dbo

.NewTblProperty_UpdateSurveyor

If I try to drop triggers that do not exist I get this:

Server: Msg 3701, Level 11, State 5, Line 1

Cannot drop the trigger 'dbo.xNewTblProperty_TriggerUpdateSurveyor', because it does not exist in the system catalog.

Server: Msg 3701, Level 11, State 5, Line 2

Cannot drop the trigger 'dbo.xNewTblProperty_UpdateSurveyor', because it does not exist in the system catalog.

I've just noticed that the first trigger does not have dbo as the prefix Would that make a difference?

Thanks for your efforts by the way.

Bill

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

I've already experienced something like this. What "made it work" was that I restarted EM and the offending objects were gone. For some reason the refresh methods didn't work in this context.

xnfec SSC Veteran Points: 271 More actions · Answer 11

This won't work here, I've been trying to do this for weeks. EM has been restarted numerous times and the server re-booted.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ok, the only other thing I can think of is trying to drop the table and recreate it (almost like EM does when you save the changes made to a table).

Maybe you can script the table with the indexes, foreign keys and constraints including the new triggers. Then create the table under MyTableName_2. Ship the data from the old table, drop the old table and then rename the new table to the correct name.

Other than that I have no idea how to help you.

Good luck.

xnfec SSC Veteran Points: 271 More actions · Answer 13

Hmm, I was saving that as a last resort in case someone could come up with and explanation and/or solution. Its looking increasingly likely that the table will have to go. still, thanks anyway.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Looks to me like you have the situation under controle... Just make sure you have a full backup of the database before reloading the table... in case another bug screws the server and you have to reinstall. It shouldn't be a problem... but then again you shouldn't have this problem in the first place, so better safe than sorry.

Let us know how this goes.