Simple Triggers to Track DML operations in Sql Server
$30-50 USD
Ditutup
Disiarkan sekitar 16 tahun yang lalu
$30-50 USD
Dibayar semasa penghantaran
Hi,
Here is what needs to be done.
1. We have a Sql Server database (Sql Server 7.0). There are about 15 tables in which we want to record which Sql server User has done what all Inserts/Updates/Deletes and store that information in an AuditTable.
2. The Audit Table has the following fields:
TableName
TablePrimaryKey
Operation
DateTimeOfOperation
XMLOfOldValues
XMLOfNewValues
SqlServerUser
3. Here is the strategy
a. Create Insert/Update/Delete Triggers for the Tables we wan to Track.
Fortunately each table has an integer primary key.
b. The Triggers record the info in the Audit table. Please note that the you can get the old and new values in a Row in form of xml like for example users table row in xml can be
<id>1</id>
<name>Hercules</name>
b. Each and every change must be recorded, so even bulk insert/updated delete operations should be recorded as separate rows in Audit table.