CODEWITHSUNDEEP
sql-servertriggerstable-locking
OldyGuy
OldyGuy

Reputation: 21

How to check if table is available to drop & create a trigger?

I've a long script in sql server 2000 (17k lines and growing).

Problem is that about the half, several triggers must be dropped & recreated as part of remote installation procedure.

Sometimes, one or two tables are in use and installation stopped without warning me, until i take a look into messages tab and found it.

I would like a semi-unattended installation and I've at least two options:

a) pessimistic approach: Confirm all tables availability at the beginning and show a warning + "set exec off" if some table is in use (very unlikely to be in use between starting and trigger creation)

b) optimistic approach: Show a warning at the beginning when some table is in use and leave it to me if installation can continue, encapsulating offending triggers with set exec off/on code. i can do this because trigger code tend to be VERY stable (several years old)

Anyway, i just need to know if table is available for trigger dropping/creating without stopping my process (table locking?). i logged into BOL & some forums but i really don't know what to look for.

update:
Use this select to find your Type_Id & Mode_Id & Sts_Id values...
(mine are 5, 6 & 1, respectively, but COULD be others, those came just from initial testing)
%%%WARNING: remember MS can CHANGE or DEPRECATE them as this is NOT the best way to do it, but the only one i've found 'til now... 

use <your_db_here>  
go  

declare @TblNam varchar(100)  
select  @TblNam = '<your_[usr.]table_name_here>'  

select  convert (smallint, l.req_spid)  spId,
        coalesce(s.loginame,'')         UsrNam,
        coalesce(s.hostname,'')         HstNam,
        l.rsc_dbid                      dbId,
        db_name(l.rsc_dbid)             dbNam,
        l.rsc_objid                     objId,
        object_name(l.rsc_objid)        objNam,
        l.rsc_indid                     indId,
        substring(v.name, 1, 4)         Type,
        substring(l.rsc_text, 1, 16)    Resource,
        substring(u.name, 1, 8)         Mode,
        substring(x.name, 1, 5)         Status,
        l.rsc_type                      Type_Id,
        l.req_mode+1                    Mode_Id,
        l.req_status                    Sts_Id

   from master.dbo.syslockinfo  l                                                   inner join  
        master.dbo.spt_values   v   on v.type = 'LR' and v.number = l.rsc_type      inner join  
        master.dbo.spt_values   x   on x.type = 'LS' and x.number = l.req_status    inner join  
        master.dbo.spt_values   u   on u.type =  'L' and u.number = l.req_mode+1  
        master.dbo.sysprocesses s   on s.spid = l.req_spid

  where db_name(l.rsc_dbid) = db_name()  
    and v.name      = 'TAB'  
    and l.rsc_objid = object_id(@TblNam)

Upvotes: 1

Views: 284

Answers (1)

OldyGuy
OldyGuy

Reputation: 21

this approach is better than digging into system tables:

set lock_timeout 1
select top 1 * from <your_[usr.]table_name_here> (tablockx)
select @@error

Upvotes: 1

Related Questions