What Are Database Triggers?

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

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.

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