Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rsteph
Starting Member
3 Posts |
 Posted - 2008-02-29 : 08:39:00
|
| I've got a ContactInfo table, that stores a variety of information about a contact (i.e. first/last name, address, phone, date of birth, status, etc.). I seem to be randomly loosing contacts though. Older backup's of my database still show a given contact, but the current one doesn't. It's happened a few times, and I can't seem to track what's causing it. (Nothing that I'm doing *should* be causing the contact to be deleted).I'm looking to create a delete trigger for the table, so that any time a record gets delete, it will record what record got deleted (contactID, firstName, lastName), as well as what time it got deleted, and if possible, what function cause the delete (what function was accessing the table when the delete happened).I've got some basic knowledge of SQL, and SQL statements, but my knowledge is limited... so any help on this would be greatly appreciated - or, if someone could point me to a website with good suggestions and examples, to help me create a trigger to monitor this stuff. I would be greatly indebted for any help that could be offered.Here is kind of a shell for a delete trigger that I have been able to put together from some various examples I've found.IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trDeleteContactInfo' AND type = 'TR') DROP TRIGGER trDeleteContactInfoGOCREATE OR REPLACE TRIGGER trDeleteContactInfoon ContactInfoFOR DELETEAS EXEC master..xp_sendmail 'my@email.com', 'Contact has just been deleted from ContactInformation Table'GO |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
|
|
|
|
|
|
|
Creating a Delete Trigger