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 |
|
scelamko
Constraint Violating Yak Guru
309 Posts |
 Posted - 2010-04-01 : 16:49:14
|
| Guys,I have to track all the DMLs on bunch of tables to see when the data got modified.I have come up with two optionsOption 1TRACK table has been created and each insert, update, delete corresponding trigger ontable registers an entry into the table with PK_KEY (PRIMARY KEY OF TABLE), TAB_NAME (TABLE NAME), DML_TYP ('D' FOR DELETE, 'I' FOR INSERT, 'U' FOR UPDATE)Below is the Update trigger CREATE TRIGGER [dbo].[TRG_U_TRACK_TABLE]ON [dbo].[TABLE]AFTER UPDATE NOT FOR REPLICATIONAS BEGIN SET NOCOUNT ON IF TRIGGER_NESTLEVEL() > 1 RETURN INSERT INTO TRACK ( PK_KEY, DML_TYP, TAB_NAME ) SELECT table_id,'U','TABLE' FROM INSERTEDENDGOOption 2For any DML I update DATE_TIME_MOD on the tableHowever, my question which is the better option in an high volume OLTP envrionment.I believe option 2 of updating itself for any DMLs is prone to blocking and deadlocks for mass updates. In terms of transactions how does the trigger updates on the table itself work as compared to inserting rows into another table by trigger to track DML operations.Any suggestions/inputs would helpThanks |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-04-01 : 16:53:49
|
| Depends on your requirements. Do you need to know the data values before and after an update throughout time? If so, then I think you need an audit table. Additionally, you cannot track deletes with a time stamp if the row is physicaly deleted.What version of SQL Server are you using? If 2008 you might take a look at Change Data Capture (CDC). |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Trigger performance: Blocks and DeadLocks