Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

How to view Trigger in Profiler

Posted on 2006-04-04
6
Medium Priority
?
1,938 Views
Last Modified: 2008-02-01
I'm trying to figure out why I can't see my trigger fire off in SQL 2005 Profiler.  Which propety should I enable the trace to monitor in order to see any triggers that have been fired on my tables?
0
Comment
Question by:dba123
  • 3
  • 2
6 Comments
 
LVL 3

Accepted Solution

by:
J2811 earned 1600 total points
ID: 16378572
Erm, not sure if this is what you are after. For DML triggers,

Knowing Triggers are really stored procedure under a disguise, using SQL Server Profiler, create new trace and trace on stored procedure such as STMTSTARTING.

For example if I have a DML trigger on Table_1,

ALTER TRIGGER [testDMLTrig]      
ON [dbo].[Table_1]
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10)

In the profiler, you would see "RAISERROR('....)" in STMTSTARTING and STMTCOMPLETED lines.

Unfortunately I don't see where in SQL Profiler allows you to see the name of the trigger though. Any experts out there that know otherwise?

Now of course the other very premitive way, is you can always insert a record into another user defined table whenever a specific trigger that you are trying to monitor get fired off.


Regards,
0
 
LVL 28

Assisted Solution

by:imran_fast
imran_fast earned 400 total points
ID: 16378767
follow these steps
=============
1. In new trace connect to server
2.In Trace properties inside profiler
3.Click on event Selection
4.Check  Show All Events (to view all the events)
5.Enable Sp_StmtStarting, Sp_StmtCompleted check box

Let us know if you need furter assistance.
0
 
LVL 3

Expert Comment

by:J2811
ID: 16378770
thank you for agreeing with me imran_fast.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 28

Expert Comment

by:imran_fast
ID: 16378834
sorry didn't read your post carefully.
0
 
LVL 3

Expert Comment

by:J2811
ID: 16414763
dba123, does this answer your question? if no, please let know.
0
 
LVL 1

Author Comment

by:dba123
ID: 16466804
well that's stupid that you can't see the name of the trigger...what a joke MS.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question