Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

can dml triggers statements get logged Expand / Collapse
Author
Message
Posted Monday, January 21, 2013 12:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:30 AM
Points: 113, Visits: 207
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?
Post #1409373
Posted Monday, January 21, 2013 1:15 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
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
Post #1409394
Posted Monday, January 21, 2013 4:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, April 14, 2014 11:49 AM
Points: 739, Visits: 2,470
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

@SeanPearceSQL

About Me


  Post Attachments 
TriggerLog.png (28 views, 6.36 KB)
Post #1409489
Posted Monday, January 21, 2013 7:46 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:30 AM
Points: 113, Visits: 207
Thanks I for your example it did help to understand practically.
Post #1409559
Posted Monday, January 21, 2013 7:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:30 AM
Points: 113, Visits: 207
Thanks
Post #1409560
Posted Monday, January 21, 2013 7:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:30 PM
Points: 41,531, Visits: 34,448
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 2008, MVP
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

Post #1409567
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse