Triggers in SQL 2005

[deswar]

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.

[ck9663]

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