Finding and Manipulating Trigger Statuses
 
In SQL Server 2000, you can attach triggers to database objects like tables to perform additional processing. For example, you can write a trigger to log entries into a database. Triggers can exist in two states, enabled and disabled. A trigger that is enabled will fire when the appropriate DML (data manipulation language) statement is used (for example, INSERT, UPDATE or DELETE). A trigger that is disabled will not fire when the appropriate DML statement is used. Sometimes it is useful to know the status of the triggers on a table or the status of all the triggers in the database. Knowing this status can be useful to write many tools. For example, if your database uses triggers and you want to provide a facility for the application administrator to enable/disable the various triggers on a need basis, you can easily write code that presents all the triggers in the database along with their current status and the administrator can adjust the status after which you execute the necessary commands against the database to enable/disable the various triggers.
 
In this article, we will see how to determine the status of the triggers for a table and for the entire database and also how you can enable/disable individual triggers.
 
Before starting on the job, let's first create a table for testing purposes. Create the following table in a database of your choice (I've used the pubs database).

IF (OBJECT_ID ('samp_test') IS NOT NULL)
	DROP TABLE samp_test
GO
CREATE TABLE samp_test
(
	colA	INT,
	colB	VARCHAR(10)
)
GO
 
We do not need any data in this table, but we will create two triggers on this table for the INSERT and UPDATE operation so that we can play around with the trigger status. Here is the code the two triggers:

-- This is the trigger for INSERT
IF (OBJECT_ID ('samp_test_insert_trigger') IS NOT NULL)
	DROP TRIGGER samp_test_insert_trigger
GO
CREATE TRIGGER samp_test_insert_trigger ON samp_test
	FOR INSERT AS
BEGIN
	SELECT * FROM inserted
END
GO
-- This is the trigger for UPDATE
IF (OBJECT_ID ('samp_test_update_trigger') IS NOT NULL)
	DROP TRIGGER samp_test_update_trigger
GO
CREATE TRIGGER samp_test_update_trigger ON samp_test
	FOR UPDATE AS
BEGIN
	SELECT * FROM inserted
END
GO
 
Having now created the respective objects, let's now write code to determine the status of the triggers on the above created table. Instead of just writing the code, we will encapsulate the code inside a user-defined function so that we can expand it later on in the code. Here is the code for the function GetTriggerStatus.

IF (OBJECT_ID ('dbo.GetTriggerStatus') IS NOT NULL)
	DROP FUNCTION dbo.GetTriggerStatus
GO
CREATE FUNCTION dbo.GetTriggerStatus (@tcTableName SYSNAME)
	RETURNS @result TABLE ([trigger name] SYSNAME, [status] VARCHAR(10), [owner] SYSNAME) AS
BEGIN
	-- Check if a table name has been passed
	IF (@tcTableName IS NOT NULL)
	BEGIN
		INSERT @result
			SELECT
				name,
				status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
					THEN 'Enabled' ELSE 'Disabled' END,
				owner = OBJECT_NAME (parent_obj)
			FROM
				sysobjects
			WHERE
				type = 'TR' AND
				parent_obj = OBJECT_ID (@tcTableName)
	END
	RETURN
END
GO
 
The code above deserves some explanation.
  • The function is implemented as a multi-statement table valued function, meaning that the return value from the function is a table after some processing steps.
  • A check is made to see if the incoming parameter to the function (which is the table name) is NULL.
  • If not, we query information from the sysobjects system table for all triggers (type = 'TR'). This table contains information on each object created in the database.
    • The name column is the name of the object.
    • The status of the trigger is found by using the OBJECTPROPERTY function. This function takes as parameter the ID of the object and the property to query. The ExecIsTriggerDisabled property finds out whether the trigger is enabled/disabled. If the value is 0, the trigger is enabled, otherwise it is disabled.
    • The owner for a trigger is found by using the parent_obj column. This contains the ID of the object to which the trigger belongs, which is the parent table. The name of the parent table is found by using the OBJECT_NAME function.
    • Finally, we need to restrict the results to only the table name that is given as input. This is done by checking the value of the parent_obj column against the table name passed as input. Note that we use the OBJECT_ID function to get the object identifier of the table name.

Let's now see how to test this fuction. Here is the code for the same and the output:


SELECT * FROM dbo.GetTriggerStatus ('samp_test')
GO

Output:
trigger name                   status     owner
------------------------------ ---------- -----------
samp_test_insert_trigger       Enabled    samp_test
samp_test_update_trigger       Enabled   samp_test

(2 row(s) affected)

In this output, we see that both the triggers are enabled. Let us see how to change the status of one of the triggers to disabled and again observe the output. Here is the code to disable a trigger.


ALTER TABLE samp_test DISABLE TRIGGER samp_test_update_trigger

The command to disable the trigger is by using the ALTER TABLE command. After this statement, if you again execute the function, you will see the following output:


SELECT * FROM dbo.GetTriggerStatus ('samp_test')
GO

Output:
trigger name                   status     owner
------------------------------ ---------- -----------
samp_test_insert_trigger       Enabled    samp_test
samp_test_update_trigger       Disabled   samp_test

(2 row(s) affected)

OK, we have now seen how to get the trigger status for a single table. Let us now extend the function to be more useful. If no parameter is passed, the function should return the trigger status of all the triggers in the database. This dual behavior will allow us to provide the tool that we discussed earlier in this article by using the same function. Here is the code for the updated function.


IF (OBJECT_ID ('dbo.GetTriggerStatus') IS NOT NULL)
	DROP FUNCTION dbo.GetTriggerStatus
GO
CREATE FUNCTION dbo.GetTriggerStatus (@tcTableName SYSNAME)
	RETURNS @result TABLE ([trigger name] SYSNAME, [status] VARCHAR(10), [owner] SYSNAME) AS
BEGIN
	INSERT @result
		SELECT
			name,
			status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
				THEN 'Enabled' ELSE 'Disabled' END,
			owner = OBJECT_NAME (parent_obj)
		FROM
			sysobjects
		WHERE
			type = 'TR' AND
			parent_obj = CASE WHEN @tcTableName IS NULL THEN parent_obj ELSE OBJECT_ID (@tcTableName) END

	RETURN
END
GO

The difference that you now see is that the WHERE clause is intelligent enough to detect if the table name passed is NULL. If so, it uses an equality condition that satisfies for all rows. Otherwise, it uses the old logic that we showed earlier. This allows to write the entire logic using only one statement. In fact, you can now easily convert this function into an inline table-valued function, which is much faster and works like a macro. Here is the updated code:


IF (OBJECT_ID ('dbo.GetTriggerStatus') IS NOT NULL)
	DROP FUNCTION dbo.GetTriggerStatus
GO
CREATE FUNCTION dbo.GetTriggerStatus (@tcTableName SYSNAME)
	RETURNS TABLE AS
	RETURN
	(
		SELECT
			name,
			status = CASE WHEN OBJECTPROPERTY (id, 'ExecIsTriggerDisabled') = 0
				THEN 'Enabled' ELSE 'Disabled' END,
			owner = OBJECT_NAME (parent_obj)
		FROM
			sysobjects
		WHERE
			type = 'TR' AND
			parent_obj = CASE WHEN @tcTableName IS NULL THEN parent_obj ELSE OBJECT_ID (@tcTableName) END
	)
GO

You can now execute this function using either a table name as a parameter or NULL as the parameter, in which case all the triggers and their statuses in the database are returned.

Well, that brings us to the end of this article. In this article we have seen how to determine the status for a trigger and have written reusable code to work for both a single table and for the entire database. Along the way, we have also seen some useful commands that help give us the property of various SQL Server objects and also the name and object identifier of database objects. Finally, we have also seen two ways of writing user defined functions. You can write many useful tools using the code presented in this article and in a future article we shall see some of them. Have fun!!

Home