September 10, 2008 at 12:36 am
i want to check last fired trigered
is there any sys table or DMV available in sql 2k5 ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 10, 2008 at 1:01 am
try querying sys.trigger_event for sql 2005 or sys.server_trigger_events
Albert B. Matubis
Database Administrator: Trend Micro PH
MCP. MCAD. MCDBA. MCT
MCTS: SQL Server 2005
MCTS: Business Intelligence
MCITP: Database Administrator
MCITP: Business Intelligence Applications Developer
September 10, 2008 at 1:10 am
Sorry it couldn't solve the problem !!!!!!!!!!:exclamationmark:
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 10, 2008 at 1:40 am
Unless you add some audit logging inside your trigger there's no way to determine which trigger fired last.
In case you have more than one trigger on a table for the same action you can set which trigger executes first or last using the procedure sp_settriggerorder.
[font="Verdana"]Markus Bohse[/font]
September 10, 2008 at 2:31 am
hi ,got ur view but
can u tell me syntatically how to use audit logging..the would be greatful:)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
September 10, 2008 at 3:03 am
Sorry but it's not easy to give you any syntax without knowing what exactly you want to know and why. If it's simly the info about which trigger was fired, then create a audit table and add some code to all your triggers which adds a record with a timestamp to the audit table when firing.
Something like
INSERT INTO audittable
Triggername, getdate()
[font="Verdana"]Markus Bohse[/font]
September 10, 2008 at 3:36 am
I agree with Markus... You need to modify your trigger to include a statement that every time a trigger is executed, you need to insert that name of the trigger, user_name(), and you can use either getdate() or taimestamp to monitor the first and last trigger that was executed. You need to create an audit table first before modifying your trigger. This is just an example....
CREATE TABLE
( Triggername nvarchar(30)
,UserName nvarchar(30)
CONSTRAINTS DF_USER DEFAULT (User_Name())
,DateExcecuted Datetime
CONSTRAINT DF_Date DEFAULT (Get_date())
)
Alter trigger
AS
INSERT
Hope it'll help...
Albert B. Matubis
Database Administrator: Trend Micro PH
MCP. MCAD. MCDBA. MCT
MCTS: SQL Server 2005
MCTS: Business Intelligence
MCITP: Database Administrator
MCITP: Business Intelligence Applications Developer
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply