UPDATE TRIGGER not in SHOWPLAN

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

UPDATE TRIGGER not in SHOWPLAN

Author

Topic

Kristen
Test

22859 Posts

Posted - 2007-05-25 : 13:42:13

I'm trying to work out where all my CPU cycles are going!

I've got an SProc that does an UPDATE statement. Its straightforward:

UPDATE U
SET ... all the columns based on @SprocParameters ...
FROM MyTable AS U
WHERE MyPK = @ThePK

but there are loads of FK constraints which involve MyTable and it has a very hairy Update Trigger.

SET SHOWPLAN_TEXT ON

shows the Query Plan for the UPDATE which contains horrific amounts of stuff related to all the FKs involved

However, it shows nothing for the UPDATE TRIGGER (unless I'm being a complete plonker).

OTOH using

SET STATISTICS IO ON; SET STATISTICS TIME ON

shows two "events", the tables involved in the UPDATE (including all the ones hauled in because of the FK constraints), and a second one which is clearly related to the Update Trigger.

Is there a simple way to get the SHOWPLAN_TEXT for the Update Trigger?

Thanks

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-25 : 14:28:07

hmm.... it shows the plan for me ok in 2k and 2k5 for this code


CREATE TABLE temp (id numeric(20, 12), NAME VARCHAR(50))
GO

CREATE TRIGGER trg ON temp
FOR UPDATE 
AS 
BEGIN 
SELECT * FROM inserted
END 
GO

INSERT INTO temp 
SELECT 0.00000000000002345, 'gbks' UNION ALL
SELECT 5, 'bdrgrs' UNION ALL
SELECT 1234, 'bvrs' UNION ALL
SELECT 125, 'esgs' UNION ALL
SELECT 33, 'htrew' UNION ALL
SELECT 33568, 'bdfs' UNION ALL
SELECT 5526, 'z43wzhg4' UNION ALL
SELECT 899, 'gdsgd' UNION ALL
SELECT 110055.4343252, 'hdysgs'

UPDATE temp 
SET NAME = 'updated name'
WHERE id = 5

SELECT *
FROM temp
WHERE id LIKE '5%'

GO
DROP table temp

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources