cannot alter instead of trigger on production table

Question

Post reply

cannot alter instead of trigger on production table

RCoston

Mr or Mrs. 500

Points: 585
More actions
May 8, 2007 at 10:40 am

#123698

Every time I attempt to alter or even drop a trigger on a table on our production database (SQL2000 sp4) it never actually alters or drops it simply blocks and locks all other processes, queries, etc... from using that table. I keep having to kill the ALTER TRIGGER or DROP TRIGGER attempt. How can I drop my trigger without kicking everyone out of production?

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

How about disabling the trigger first (alter table tblname disable trigger trname).

Then dropping/modifying it.

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 2

Thanks for the suggestion, tried:

ALTER

TABLE Products DISABLE TRIGGER iu_Products_CartPrice;

still locked/blocked everyone using the web because the Products is used to show our products on the web when customers come to it...

We get several thousand hits per day.

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

There seriously can't be that many hits a second to continuously block that object. Are you sure that there isn't a dead lock already in place???

RCoston Mr or Mrs. 500 Points: 585 More actions · Answer 4

There are no deadlocks/locks/blocks until I attempt to alter/drop/disable that trigger, than my PID takes over and just simply locks and hangs everyone and everything wanting to touch the Products table. The Products table is used for more than the web since it is the main table used by all applications web or internal which contains a current list of the company's 60,000 SKU's and their attributes. This table is used by many processes and applications beside the web.

I am left to wake up tonight and kick everyone off the database and make my change to this table before the backup runs, boy I can't wait to get to Oracle 10g. I've just about had it with SQL Server. MS-SQL Server is good for small to mid-sized activity but it's lock escalation is very seriously beginning to become a problem for us, noted by the inability to modify this simple trigger. thanks for responding...

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

I can't wait to see what other dbas have to say about this one. I'm sure there must be a way for you to make this change without stopping the app from running. I'm just not that good with locks in sql server to be of much help.