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 2005 Forums
 Transact-SQL (2005)
 locate triggers inserting records in a table?

Author  Topic 

sunil
Constraint Violating Yak Guru

282 Posts
Posted - 2008-02-14 : 04:57:08
How can I know how many triggers in my database are causing Insert, update or delete in a particular table? For example: I am having my table tbl_Comments. Records in it are inserted when couple of other tables (which I don't know) are updated. How can I get the name of these tables on whose insert/update my table "tbl_Comments" is getting inserted/updated. Hope I have made my query clear?

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-02-14 : 05:01:34
Cant you view the content of the trigger using sp_helptext 'trigger_name'?

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-02-14 : 05:09:15
One method is to have a varchar column in your table called Modified_By and populate it with name of table whose trigger action caused the insertion action onto table in trigger code.
Go to Top of Page

sunil
Constraint Violating Yak Guru

282 Posts
Posted - 2008-02-14 : 05:19:22
quote:
Originally posted by visakh16

One method is to have a varchar column in your table called Modified_By and populate it with name of table whose trigger action caused the insertion action onto table in trigger code.


I am not able to get what you mean by "onto table in trigger code.
". My objective is to find triggers which are causing insert updates to my table. I have identified 4 tables which have triggers updating my table. I want to know how many more tables are there which will cause insert/update to my table.

Madhi: I am looking for triggers causing updates. Not sure what you mean by "Cant you view the content of the trigger using sp_helptext 'trigger_name'?" I myself is looking for 'trigger_name'.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-02-14 : 05:31:15
quote:
Originally posted by sunil

quote:
Originally posted by visakh16

One method is to have a varchar column in your table called Modified_By and populate it with name of table whose trigger action caused the insertion action onto table in trigger code.


I am not able to get what you mean by "onto table in trigger code.
". My objective is to find triggers which are causing insert updates to my table. I have identified 4 tables which have triggers updating my table. I want to know how many more tables are there which will cause insert/update to my table.

Madhi: I am looking for triggers causing updates. Not sure what you mean by "Cant you view the content of the trigger using sp_helptext 'trigger_name'?" I myself is looking for 'trigger_name'.



ok. imagine this scenario. you have a table t which is populated by three triggers on other three tables (t1,t2,t3). you need to track which tables trigger action caused a particular records insert in t.

let trig1,trig2,trig3 be triggers on t1 t2 t3 which causes insert on t then have a new column modified_by in t of type varchar(50) (say)


then trig1 you will have


CREATE TRIGGER trig1 on t
AFTER INSERT/UPDATE,..
AS
....
other code...

INSERT INTO t (other fields,..,modified_by)
SELECT other values,
't1 insertion'
FROM INSERTED
GO

similarly you will have for trig2


CREATE TRIGGER trig2 on t
AFTER INSERT/UPDATE,..
AS
....
other code...

INSERT INTO t (other fields,..,modified_by)
SELECT other values,
't2 insertion'
FROM INSERTED
GO

and so on


if you look at records of t you will have

field1 field2 .... modified_by
val1 val2 ...... t1 insertion
val4 val5 ...... t2 insertion
val6 val7 ...... t1 insertion
val1 val2....... t3 insertion
val1 val2 ...... t2 insertion
val1 val2 ...... t1 insertion
....
Go to Top of Page

sunil
Constraint Violating Yak Guru

282 Posts
Posted - 2008-02-14 : 05:45:46
Oh! It seems I am not good at writing. You are partially correct Visakh. My problem is that I don't know(numbers) by how many triggers my table t is getting populated. Till now i am manually searching tables to find triggers updating my table. I am looking for a quick SQL way of determining number of triggers updating my table. Hope this time I am spot on with my question.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-02-14 : 06:00:36
See if this helps

select object_name(s.id) as trigger_name,c.text from syscomments c inner join sysobjects s
on c.id=s.id where xtype='tr' and c.text like '%update%tbl_Comments%'

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sunil
Constraint Violating Yak Guru

282 Posts
Posted - 2008-02-14 : 06:20:49
quote:
Originally posted by madhivanan

See if this helps

select object_name(s.id) as trigger_name,c.text from syscomments c inner join sysobjects s
on c.id=s.id where xtype='tr' and c.text like '%update%tbl_Comments%'

Madhivanan

Failing to plan is Planning to fail


Thanks, It lists all triggers which updates my table. I modified it to get triggers which do Insert also.
select object_name(s.id) as trigger_name,c.text from syscomments c inner join sysobjects s
on c.id=s.id where xtype='tr' and c.text like '%update%Emp1%'
union
select object_name(s.id) as trigger_name,c.text from syscomments c inner join sysobjects s
on c.id=s.id where xtype='tr' and c.text like '%Insert%Emp1%'
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-02-14 : 06:27:33
Also

select object_name(s.id) as trigger_name,c.text from syscomments c inner join sysobjects s
on c.id=s.id where xtype='tr' and (c.text like '%update%Emp1%' or c.text like '%Insert%Emp1%')


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

sunil
Constraint Violating Yak Guru

282 Posts
Posted - 2008-02-14 : 06:35:08
I saw execution plan for both the queries with union and suggested by madhi. How do I know which one is efficient? Also in general, how does one interpret execution plan. It goes over my head when I see it. Any references as to how to interpret execution plan and choose best way.
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2008-02-14 : 07:21:43
quote:
Originally posted by sunil

I saw execution plan for both the queries with union and suggested by madhi. How do I know which one is efficient? Also in general, how does one interpret execution plan. It goes over my head when I see it. Any references as to how to interpret execution plan and choose best way.

Do search here http://www.sql-server-performance.com
You can find lot of articles

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site