Triggers 2

[DirtyB]

I have just "inherited" a new very large SQL 7 database. I have heard that there are SEVERAL triggers in place. How can I see how many there are, and then the code for each one?

 

[tlbroadbent]

Run sp_helptrigger @tabname = 'table' to view list of triggers on a table.

You can examine the trigger code in Enterprise Manager. Or generate SQL scripts for tables, including triggers, from Enterprise manager. Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot

 

[DirtyB]

Thanks, I think that you always answer my questions in here. It is much appreciated. The problem is that this database has almost two hundred tables. I need a list of triggers, and what table they correspond to. How can you find triggers in Enterprise Manager, I can't seem to find anything about them.

thanks

 

[tlbroadbent]

In Enterprise manager, right-click a table, move to All Tasks and select Manage Triggers. Select Trigger names from the drop down box to view the Trigger code.

Alternatively, right-click the database name, move to All Tasks and select Generate SQL Scripts. Choose All Tables. Click on the Options tab and select Scrip Triggers. Click OK to create the script. The script will contain table and trigger creation language.

The following query will list all the Triggers in the database with owners and tables.

select cast(d.name + '.' + b.Name As varchar) As TableName,
cast(c.name + '.' + a.name As varchar) as TriggerName
from sysobjects a Join sysobjects b On a.parent_obj=b.id
Join sysusers c on a.uid=c.uid
Join sysusers d on b.uid=d.uid

where a.type='tr' Terry
------------------------------------
Blessed is the man who, having nothing to say, abstains from giving us worthy evidence of the fact. -George Eliot