can dml triggers statements get logged

Question

can dml triggers statements get logged

sej2008

SSCarpal Tunnel

Points: 4495
More actions
January 21, 2013 at 12:24 am

#287828

I want to understand since dml triggers are using data modification statements
are they logged in log file and if yes when?
and can we log those trigger events in auditing in system event log in sql 2008?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

sej2008 (1/21/2013)
I want to understand since dml triggers are using data modification statements
are they logged in log file and if yes when?

i dont think they are differently/specially get treated for transational logging. their logging works same as other transactions.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Sean Pearce SSCoach Points: 15999 More actions · Answer 2

USE [AdventureWorks];

GO

CREATE TABLE TestTrigger (ID INT)

GO

CREATE TABLE TestTriggerAudit (ID INT)

GO

CREATE TRIGGER trTestTrigger ON TestTrigger

FOR INSERT

AS

INSERT INTO TestTriggerAudit SELECT * FROM inserted;

GO

INSERT INTO TestTrigger VALUES (1);

GO

CHECKPOINT;

INSERT INTO TestTrigger VALUES (22);

DBCC LOG('AdventureWorks', 3);

DROP TABLE TestTrigger;

DROP TABLE TestTriggerAudit;

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

sej2008 SSCarpal Tunnel Points: 4495 More actions · Answer 3

Thanks I for your example it did help to understand practically.:-)

sej2008 SSCarpal Tunnel Points: 4495 More actions · Answer 4

sej2008

SSCarpal Tunnel

Points: 4495

January 21, 2013 at 7:47 am

#1579257

Thanks:-)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

sej2008 (1/21/2013)
I want to understand since dml triggers are using data modification statements
are they logged in log file and if yes when?
and can we log those trigger events in auditing in system event log in sql 2008?

Just one point...

The transaction log is not an audit log. It's not there so you can see what happened. If you want an audit log, then configure one using trace, extended events, CDC, change tracking or SQL Audit.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass