Finding disabled triggers - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Finding disabled triggers

Author

Topic

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-10-29 : 14:36:14

Is there any proper way of finding disabled triggers in a database?

I can't see anything in BOL, but I might not be searching for the right things. Kalen Delaney has some words of wisdom about triggers in Inside SQL Server 2000, but doesn't mention this specifically.
Experimenting, it seems that one of the flags on the status column of sysobjects gets set, but doing


SELECT *
FROM sysobjects
WHERE type = 'TR'
  AND status & 2048 <> 0

seems like a nasty hack to me.
Any pointers?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-10-29 : 14:55:18

I searched and searched, but I couldn't find anything that would give you what you want. There are definitely times when you need to get data directly from the system objects because the INFORMATION_SCHEMA views do no provide you with everything. I thought maybe sp_helptrigger would work, but it doesn't tell you if it is disabled or not.

Tara

REDDY
Starting Member

43 Posts

Posted - 2003-10-29 : 20:05:17

I too got this problem once,so worked around with sp_helptrigger and developed the following one,it may help you

sp_helptriggerstatus 'ordermaster' --will give trigger status for perticular table

sp_helptriggerstatus 'all' -- all triggers status

CREATE procedure sp_helptriggerstatus --- 2003/04/24
@tabname nvarchar(776), /* Table name */
@triggertype char(6) = NULL /* Trigger type */
as

declare @objid int, /* id of the object */
@dbname sysname,
@deltrig int,
@instrig int,
@updtrig int

-- Check to see that the object names are local to the current database.

select @dbname = parsename(@tabname,3)

if @dbname is not null and @dbname <> db_name()
begin
raiserror(15250,-1,-1)
return (1)
end

if not UPPER(@tabname) = 'ALL'
select @objid = id from sysobjects where id = object_id(@tabname)
and type in ('S','U', 'V')

if @objid is null AND NOT UPPER(@tabname) = 'ALL'
begin
select @dbname = db_name()
raiserror(15009,-1,-1,@tabname,@dbname)
return(1)
end

/* Check that input type is UPDATE, INSERT, DELETE */
if @triggertype is not null and not UPPER(@triggertype ) in ('UPDATE', 'INSERT', 'DELETE')
begin
raiserror(15305,-1,-1)
return(1)
end

if @triggertype is NULL
begin

if UPPER(@tabname) = 'ALL'
select
trigger_name = name,
trigger_owner = user_name(uid),
isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),
isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger'),
TriggerStatus = case when OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 0 then 'Enabled'
else 'Disabled'
end

from sysobjects where type = 'TR'

else

select
trigger_name = name,
trigger_owner = user_name(uid),
isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),
isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger'),
TriggerStatus = case when OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 0 then 'Enabled'
else 'Disabled'
end

from sysobjects where parent_obj = @objid and type = 'TR'

end

else
begin
set @deltrig = case
when upper(@triggertype ) = 'DELETE' then 0
else -1 end
set @instrig = case
when upper(@triggertype ) = 'INSERT' then 0
else -1 end
set @updtrig = case
when upper(@triggertype ) = 'UPDATE' then 0
else -1 end

if UPPER(@tabname) = 'ALL'
select
trigger_name = name,
trigger_owner = user_name(uid),
isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),
isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger'),
TriggerStatus = case when OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 0 then 'Enabled'
else 'Disabled'
end
from sysobjects
where
ObjectProperty( id, 'ExecIsDeleteTrigger') > @deltrig and
ObjectProperty( id, 'ExecIsInsertTrigger') > @instrig and
ObjectProperty( id, 'ExecIsUpdateTrigger') > @updtrig and
type = 'TR'
else

select
trigger_name = name,
trigger_owner = user_name(uid),
isupdate = ObjectProperty( id, 'ExecIsUpdateTrigger'),
isdelete = ObjectProperty( id, 'ExecIsDeleteTrigger'),
isinsert = ObjectProperty( id, 'ExecIsInsertTrigger'),
isafter = ObjectProperty( id, 'ExecIsAfterTrigger'),
isinsteadof = ObjectProperty( id, 'ExecIsInsteadOfTrigger'),
TriggerStatus = case when OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 0 then 'Enabled'
else 'Disabled'
end
from sysobjects
where parent_obj = @objid and
ObjectProperty( id, 'ExecIsDeleteTrigger') > @deltrig and
ObjectProperty( id, 'ExecIsInsertTrigger') > @instrig and
ObjectProperty( id, 'ExecIsUpdateTrigger') > @updtrig and
type = 'TR'

end
return(0) --sp_helptrigger

GO

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-10-30 : 04:01:49

quote:
OBJECTPROPERTY(id, 'ExecIsTriggerDisabled')

Thanks, that's exactly what I was looking for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources