By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
460,023 Members | 1,297 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 460,023 IT Pros & Developers. It's quick & easy.

Triggers in SQL 2005

P: 1
I have a table(ACCOUNT) which has 30+ columns. There can be Inserts/Updates/Deletes to this table. My requirement is to create triggers for Insert/Update/Delete and store previous value and latest value for only the affected fields into ACCOUNT_AUDIT table

Example for ACCOUNT table

Field1 Field2 Field3 Field4 Field5
a b c d e ---->Old data

Field1 Field2 Field3 Field4 Field5
z b c y e ---->modified data


ACCOUNT_AUDIT table has 3 fields
Action,PreviousValue,NewValue,LastChangeDate

After applying the trigger on ACCOUNT table, ACCOUNT_AUDIT table should populate the data as follows.

Action: Update

PreviousValue:<Previous><Field1>a<Field1><Field4>d </Field4></Previous>

NewValue:<New><Field1>z<Field1><Field4>y</Field4></New>

LastChangeDate: 01/30/08 07:30:53.000


Can any one please help me to write trigger like this?
Many thanks in advance.
Jan 30 '08 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
I have a table(ACCOUNT) which has 30+ columns. There can be Inserts/Updates/Deletes to this table. My requirement is to create triggers for Insert/Update/Delete and store previous value and latest value for only the affected fields into ACCOUNT_AUDIT table

Example for ACCOUNT table

Field1 Field2 Field3 Field4 Field5
a b c d e ---->Old data

Field1 Field2 Field3 Field4 Field5
z b c y e ---->modified data


ACCOUNT_AUDIT table has 3 fields
Action,PreviousValue,NewValue,LastChangeDate

After applying the trigger on ACCOUNT table, ACCOUNT_AUDIT table should populate the data as follows.

Action: Update

PreviousValue:<Previous><Field1>a<Field1><Field4>d </Field4></Previous>

NewValue:<New><Field1>z<Field1><Field4>y</Field4></New>

LastChangeDate: 01/30/08 07:30:53.000


Can any one please help me to write trigger like this?
Many thanks in advance.
pseudo-code:

1. create your trigger
2. have your "audit table" ready. this table should have the same structure as your transaction table with an extra field to store the string "Previous Value" and "New Value".
3. when your trigger fire, you can check if the field you want to monitor is the one updated or you don't have to and let the trigger fire regardless of the column updated....
4. for insert, do you need to create an audit record? if yes, insert the entire value of inserted table adding the "New Value" string to go to your extra field.
5. for deleted, do you need to create an audit record? if yes, insert the entire value of delete dtable adding the "Previous Value" string to go to your extra field.
6. for update, insert the entire deleted table adding the "Previous Value" to your extra string and then add the inserted table adding the "New Value" to your extra string...

again, this is pseudo-code...sorry, i don't spoon-feed ;) i just point to the a possible solution...if you hit a wall, post what you have so far, and we can debug your code for you...

-- ck

-- ck
Jan 30 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.