March 9, 2009 at 1:27 am
Dear All,
I have a problem :
I want to write a Trigger which gives the information of the action
done on a database through mail.
Brief Description : I need to find the information of the user who has
done any action on database as ex: update , del, modify, copy, move,
... anything done on SQL Server 2005 database.
And i would like to have same info to be stored in a table as userid,
hostname, command, time, - and the information stored in the table
must be fired to mail id.
Trying much to work on this. Suggest me the process how can that be
done or possible.
Help required immediately.
Thanks,
Cheers - CH&HU
March 9, 2009 at 6:54 am
I want to make sure I understand your requirements.
1. You want to log all actions done by any user to any table in a SQL Server database.
2. You want to email a specific person or persons whenever any action is done to any table.
Can you clarify what you mean by copy and move?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 9, 2009 at 6:54 am
Have you looked at DDL triggers yet? They can help you log the information to the table. Then all you have to do is schedule a job that checks for any table additions between X & Y time and sends the email off.
March 9, 2009 at 10:13 pm
Thanks Jack,
For your reply. I mean when ever a tables are exported and imported.
I treid DDL triggers but, here there is a lot of hell like mails delivered for each row update. I mean in a table having 50 columns then if a single row is updated, its sending the mails for 50.
My requirement is i need to get the info about only the table is updated as you said.
Please help me regarding this.. any script or any process for this..
Thanks,
CH&HU
March 9, 2009 at 10:14 pm
Thanks for the reply Tarvin,
Please find the post early today.
Thanks,
CH&HU.
March 10, 2009 at 4:37 am
I'm going to reiterate what I said earlier.
Use DDL triggers to log changes to a table in an Admin database. Use a Datetime column to log the time the changes happen.
Create a job with a T-SQL step. Have it select from your log table where DateCol is greater than or equal to yesterday (check out the DateAdd() and GetDate() functions in Books Online) and DateCol is less than or equal to "now". Then have it send ONE email via the dbmail proc (check out database mail in Books Online for more details) with a list of all the changes.
Schedule the job to run once a day.
If you want to run the job every hour or every few hours, schedule it that way and change the DateAdd() code to reflect that. This way you only get one email with all your changes all at once (not fifty emails).
I'm not posting the code because it's a fairly simple thing to do. You can get all the information from Books Online. If you've looked at DDL triggers already, a SELECT and exec of the dbmail proc should be easy.
March 10, 2009 at 5:57 am
Thanks Much Tarvin,
Let me try this way...
I have already created one script but it mailed like hell as .... Pls check the below script created...
------------------------------------------------------------------------------------------------------------------------
USE TEST;
GO
IF OBJECT_ID ('Branches','TR') IS NOT NULL
DROP TRIGGER Branches;
GO
CREATE TRIGGER Branches
ON Reports_AIR
AFTER INSERT, UPDATE, DELETE
AS
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'CH&HU_Local',
@recipients = 'ch.hu@smartvalue.com',
@body = 'Print the report',
@subject = 'Database_Reminder';
GO
---------------------------------------------------
The script worked perfectly, but the mails received are like
bunches... as i have 120 columns per table and it shooted 30 mails as
some fileds are updated. 🙁
My Criteria is to get the info if a row is updated : ex: if a row is
updated in a table i need to get the details of the row which was
updated. only a single message.
Please keep posting the updates and the processes you are well about
this............ Fast replies are appreciated.........
March 10, 2009 at 7:12 am
The trigger you posted here should only send out an email once per update, not once per updated column.
What Brandie is suggesting is that you create a staging table that either contains the values that you want and use the trigger to populate that table, including a change date. Then use a SQL Server Agent job to process those rows and send you a summary each X days/hours/minutes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 10, 2009 at 7:24 am
What Jack said. I'm not suggesting a Trigger at all. In fact, I strongly urge you not to use a trigger. Triggers play merry heck with the database processing power.
A simple SELECT statement inside a SQL Job step will be much more efficient and easier to manage.
And you can use dynamic SQL to set up your mail proc.
March 10, 2009 at 7:25 am
ACK! let me rephrase.
I'm suggesting you use the DDL trigger to update the staging table. Then use a normal T-SQL SELECT within a job to get the staged data and send one email.
Not use a trigger to send the email.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply