Trigger Management - Listing Triggers
(Page 4 of 4 )
To list triggers associated with a table, use the system stored procedure sp_helptrigger:
Exec sp_helptrigger 'dbo.OrderHeader'
The server returns the list of triggers associated with the specified table and displays the type of trigger found in the isupdate, isdelete, isinsert, isafter, and isinteadof columns (see Figure 9-3).
Viewing Triggers
You can obtain the code for a trigger using the system stored procedure sp_helptext:
Exec sp_helptext 'dbo.trOrderStatus_U'
Figure 9-3. Trigger information from sp_helptrigger
The server returns the code for the specified trigger:
Text
------------------------------------------- CREATE Trigger trOrderStatus_U
On dbo.[OrderHeader]
After Update -- For Update
As
If Update (OrderStatusId)
Begin
Insert into ActivityLog
( Activity,
LogDate,
UserName,
Note)
Select
'OrderHeader.OrderStatusId',
GetDate(),
USER_NAME(),
'Value changed from '
+ Cast( d.OrderStatusId as varchar)
+ ' to '
+ Cast( i.OrderStatusId as varchar)
From deleted d inner join inserted i
On d.OrderId = i.OrderId
End
Deleting DML Triggers
A trigger can be deleted, as can all other database objects, using the appropriate Drop statement:
if exists (select * from sys.objects
where object_id = object_id(N'[dbo].[trEquipment_IU]')
and type in (N'TA', N’TR'))
DROP TRIGGER [dbo].[trEquipment_IU]
Deleting DDL Triggers
The statement for dropping DDL triggers contains a reference to the scope (level) of the trigger:
if exists (select * from master.sys.server_triggers
where name = N'trdTestCreateLogin2' and parent_class=100)
DROP TRIGGER [trdTestCreateLogin2] ON ALL SERVER
GO
if exists (select * from sys.triggers
where name = N'trdAuditTableChanges' and parent_class=0) DROP TRIGGER [trdAuditTableChanges] ON DATABASE
NOTE
You should check the presence of server-level triggers in the master.sys.server_triggers view and the presence of database-level triggers in the sys.triggers view.
Please check back next week for the continuation of this article.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
|
 This article is excerpted from chapter nine of the book Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL and .NET, written by Dejan Sunderic (McGraw-Hill/Osborne, 2006; ISBN: 0072262281). Check it out today at you favorite bookstore. Buy this book now.
|
|
