triggers information - 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 Development (2000)

triggers information

Author

Topic

mobasha
Starting Member

45 Posts

Posted - 2007-05-07 : 09:41:19

i have wrote a code to disable all the triggers on the database tables
and i want to get the information about the triggers current state
,is there is any function or view that can help me out???
and this is the code :
create procedure disabling_trigger (@server varchar(50),@database varchar(50),@owner varchar(20))
as
declare @sqlstring varchar(1000)
begin
set @sqlstring='
declare curs_trigger cursor for select name from '
+@server+'.'+@database+'.'+@owner+'.sysobjects where name not like ''dts%''
and xtype=''u''

declare @table_name varchar(500)
open curs_trigger
fetch next from curs_trigger into @table_name
while(@@fetch_status=0)
begin
declare @x varchar(1000)
set @x=''alter table ''
+@table_name+ '' disable trigger all''
exec (@x)
fetch next from curs_trigger into @table_name
end
close curs_trigger
deallocate curs_trigger'
exec (@sqlstring)
end

MobashA

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 09:42:36

What do you mean by "state" of trigger?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-07 : 09:45:00

[code]
select
trigger_name = name,
trigger_owner = user_name(uid),
Is_Disabled = case when OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 1 then 'Yes' else 'No' end
from sysobjects
where parent_obj = object_id(@objname) and type = 'TR'
[/code]

Chirag

http://chirikworld.blogspot.com/

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-07 : 09:53:24

Be sure to take a note of the issue with Disable Trigger here:

[url]http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/10/27/1248.aspx[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

mobasha
Starting Member

45 Posts

Posted - 2007-05-07 : 13:06:01

thanks alot i think Chirag this is going to help

MobashA

mobasha
Starting Member

45 Posts

Posted - 2007-05-07 : 13:08:43

i mean by state is it disabled or enabled

MobashA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources