|
Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor for listing of trigger definitions which apply stored procedures (stored procedure name found in definition text).
-- List all triggers definition which use one or more stored procedure
-- System views: sys.triggers, sys.procedures
USE AdventureWorks2008;
SELECT DISTINCT Sproc=p.name, TrgText= OBJECT_DEFINITION(t.object_id)
FROM sys.triggers t
INNER JOIN sys.procedures p
ON PATINDEX('%'+p.name+'%', OBJECT_DEFINITION(t.object_id)) > 0
WHERE p.TYPE='P'
ORDER BY TrgText, p.Name
/* uspPrintError CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder]
AFTER INSERT AS BEGIN DECLARE @Count int; SET @Count = @@ROWCOUNT;
IF @Count = 0 RETURN; SET NOCOUNT ON; BEGIN TRY
INSERT INTO [Production].[TransactionHistory]( [ProductID] ,
[ReferenceOrderID] ,[TransactionType] ,[TransactionDate] ,[Quantity] ,
[ActualCost]) SELECT inserted.[ProductID] ,inserted.[WorkOrderID] ,'W' ,
GETDATE() ,inserted.[OrderQty] ,0 FROM inserted; END TRY
BEGIN CATCH EXECUTE [dbo].[uspPrintError]; IF @@TRANCOUNT > 0
BEGIN ROLLBACK TRANSACTION; END EXECUTE [dbo].[uspLogError];
END CATCH; END;
......
*/
------------ |
