Querying the sys tables to find out those triggers not for replication
-
Happy New Year Everyone!
Does anyone know how to find out those triggers that are not for replication using the sys tables on sql server 2000? Your help will be truly appreciated as I have been trying for quite a while but to no avail. I'm sure there must be someone out there who can help
Thanks
Netania
-
select name, replinfo from sysobjects where xtype='TR'
this will return all triggers, and a zero or one value for the replinfo.
if replinfo is [zero], the "not for replication" is true.
dbname triggername replinfo msdb trig_backupset_delete 0 msdb trig_targetserver_insert 0 msdb trig_notification_ins_or_upd 0 msdb trig_notification_delete 0 edited: this will give the db name and the table the trigger is related to:
select db_name() as db,object_name(parent_obj) as tablename,name, replinfo from sysobjects where xtype='TR'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
hi Lowell,
Thank you for your help. I tested your script but all the results are shown to be 0, eve the ones that are true. I modified the query so that it will only show those generated by the user instead of the system.
select db_name() as db,object_name(parent_obj) as tablename,name, replinfo
from sysobjects
where xtype='TR' and name not like '%trg%'
Thanks,
Netania
-
im sorry i kind of confused you; i got the vbalues inverted.
0 in the replinfo means not replicated.
a non zero value means it is being replicated.
here are some of the values I know for replinfo values:
1=transactional - table or indexed view - log based
3=transactional - table or indexed view - log based with custom sync object
33=transactional - table or indexed view - immediate updating
35=transaction- table or indexed view - log based with custom sync object and custom filter
64 - procs used by immediate updating
128 = merge - table or indexed view
129 =merge and transactional - table or indexed view
512 - all other procsprocs, views, functions
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
That was great. Thanks once again for your help.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply