Getting DML and DDL operations without using triggers

Question

Post reply

Getting DML and DDL operations without using triggers

gedda

SSC Rookie

Points: 31
More actions
November 23, 2010 at 5:12 pm

#226928

Hi I am wondering if there is a way to get events from the database for DDL and DML operations without using triggers or is this a futile ambition?

Viewing 7 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323547 More actions · Answer 1

adding traces is the way to go.

the default trace captures DDL statements, but rolls nover fairly quickly.

you can add your own trace to capture all DML statements, or filter them for what you are after.

if you have SQL2008, there some additional options as well.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

gedda (11/23/2010)
Hi I am wondering if there is a way to get events from the database for DDL and DML operations without using triggers or is this a futile ambition?

Any specific reason to avoid trigger ?

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Bhuvnesh (11/24/2010)
gedda (11/23/2010)
Hi I am wondering if there is a way to get events from the database for DDL and DML operations without using triggers or is this a futile ambition?
Any specific reason to avoid trigger ?

I can think of a few, including production server source control lockdown/change control, and possible maintainability by junior staff.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Craig Farrell (11/24/2010)
Bhuvnesh (11/24/2010)
gedda (11/23/2010)
Hi I am wondering if there is a way to get events from the database for DDL and DML operations without using triggers or is this a futile ambition?
Any specific reason to avoid trigger ?
I can think of a few, including production server source control lockdown/change control, and possible maintainability by junior staff.

In either ways, we would be putting extra overhead (while we doing these kind of auditing).

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

gedda SSC Rookie Points: 31 More actions · Answer 5

The main reason is that I wish to avoid the overhead of using trigger for performance reasons.

gedda SSC Rookie Points: 31 More actions · Answer 6

Hi all,

thanks for your responses thus far

I should have also been a touch more specific of my requirement.

Running in SQL Server 2008, and I do not have the luxury of admin rights to the server.

but what I am trying to achieve if possible is basically 2 things.

1. Logging all DDL transactions from a database or all databases (if at all feasible)

and

2. Logging all DML transactions from all tables within a database and possbily extending to all databases.

And seeing if it is possible to avoid using triggers.

Also, I am less familiar with SQL Server as I am usuallly working with other db vendors

Cheers