January 21, 2008 at 2:43 am
Hi,
I have many tables in my database, I want to know which value is getting change on each table when a particular transaction or process is happening.
January 21, 2008 at 2:59 am
Microsoft has introduced DDL Trigger in SQL Server 2005. You can use DDL Trigger to fulfill your requirement.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 21, 2008 at 3:06 am
From the link :http://msdn2.microsoft.com/en-us/library/ms189799.aspx
Using a DML trigger with a reminder e-mail message
The following example sends an e-mail message to a specified person (MaryM) when the Customer table changes.
USE AdventureWorks
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
DROP TRIGGER Sales.reminder2
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'AdventureWorks Administrator',
@recipients = 'danw@Adventure-Works.com',
@body = 'Don''t forget to print a report for the sales force.',
@subject = 'Reminder';
GO
"More Green More Oxygen !! Plant a tree today"
January 21, 2008 at 3:36 am
Thanks for the correction Minaz. It's DML trigger.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
January 21, 2008 at 4:16 am
a classic DML-trigger will provide auditing capacity;
Keep in mind to keep your trigger as short as possible, local, ...
because it is executed within transaction context. If your trigger fails, the transaction fails, modifications will get rolledback !
You can indeed perform almost anything within a trigger, like sending a mail, but if e.g. your mailsystem is in maintenance, .. your audit will fail !
We prefer a simple write to an audit table (keyvalues or inserted.*, deleted.* and the who-done-it and when information).
SQLAgent jobs then send the needed mails when they run (every x minutes).
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 21, 2008 at 5:44 am
The last post is getting at the root of what can be a problem with triggers. If you are truly doing an auditing process, a regular DML trigger that will rollback the original transaction if there is an error is a good approach - if something fails in the audit you may want to stop the transaction so your auditing is reliable.
If this is not really a concern, adding triggers can be a risk of creating errors in the audit process or simply adding overhead to your transactions. Service Broker is great for helping with this. You can use a trigger to send to a service broker queue and then the queue can be processed as resources are available. Service broker will ensure you do not lose information and will keep the processing out of your original transaction.
There are articles on the web referring to service broker and asynchronous triggers that can help you get this set up.
January 22, 2008 at 9:11 am
You could also use a product like ApexSQL Audit, which can handle the grunt work for you and provide other nice features as well.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply