Find all triggers in a SQL Server Database

, 2014-11-05 (first published: )

What Are Database Triggers?

Do you know what triggers lurk in your database?

Do you know what triggers lurk in your database?

Triggers can be implemented to enforce business rules or referential data integrity in database applications.

There are even triggers that allow data modifications to multiple base tables of a view. I have actually used this in the past when working with 3rd party encryption tools prior to SQL 2005’s native encryption options.

Triggers are quite capable of being very powerful tools in your SQL Server database and application. There is a lot of debate on the use of triggers, but I’m not going to go digging into that in this post. In general they are preferred as a last resort if no other technique can be used to accomplish what you are trying to do (Foreign Keys, Stored Procedures, etc).

In general, I feel that triggers can be like hidden code in your SQL Server database. They are able to modify the way SQL Server behaves at a tabular level by overriding your original SQL code that inserts, updates or deletes data. Unless you know what each and every trigger in your database does, you run the risk of spending a lot of time trying to figure something out when things get wonky in your database or application. The key to being able to know what the triggers are doing, is to know what triggers are in your database, their type, as well as if they are active or not.

OMG! How Do I Find The Triggers In My Database?

You have to dig in the GUI to find triggers on tables and views

You have to dig in the GUI to find triggers on tables and views

Well, that’s where things get interesting. You can use the GUI in SSMS, but you have to dig through the tables & views to find the triggers. They aren’t in the same locations as stored procedures or functions. This isn’t much of an issue if you are working in a smaller database with a handful of tables. The issue arises when you have hundreds, or even thousands, of tables and views. Some may contain triggers, some may not. The only way you’ll know is to check each object for triggers manually by opening and closing the sub folders in the GUI, is in this picture.

To make things easier though, we can use the SYS.OBJECTS table to list out all the triggers within our database, include the table they are on as well as their type and status.

The following script will identify all the triggers in your database tables and indicate their type as well as if they are enabled or not.

SELECT  table_name = OBJECT_NAME(parent_object_id) ,
        trigger_name = name ,
        trigger_owner = USER_NAME(schema_id) ,
        OBJECTPROPERTY(object_id, 'ExecIsUpdateTrigger') AS isupdate ,
        OBJECTPROPERTY(object_id, 'ExecIsDeleteTrigger') AS isdelete ,
        OBJECTPROPERTY(object_id, 'ExecIsInsertTrigger') AS isinsert ,
        OBJECTPROPERTY(object_id, 'ExecIsAfterTrigger') AS isafter ,
        OBJECTPROPERTY(object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof ,
        CASE OBJECTPROPERTY(object_id, 'ExecIsTriggerDisabled')
          WHEN 1 THEN 'Disabled'
          ELSE 'Enabled'
        END AS status
FROM    sys.objects
WHERE   type = 'TR'
ORDER BY OBJECT_NAME(parent_object_id)

Once you have identified the triggers, you can look more deeply into what they actually do and determine if they are truly required, or just “forgotten” code in your database.

Regardless, knowing what code is in your database, be it triggers or not, and what it does is important. Especially when it comes to troubleshooting odd activities.

Read the full article here

The post Find all triggers in a SQL Server Database appeared first on .

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads