fuerteventura
asked on
Trigger old value, new value, PK
Being very new to SQL Server 2005, I am creating a dml trigger on one of my tables. When ativated, i want to obtain the old value (if one exists) and the new value of data that has been entered and the primary key. This is all for auditing purposes. So if you changed hair colour from Brown to blonde and the person id was 1 then the audit row would look something like this:
PK Oldvalue NewValue
1 Brown Blonde
Many thanks for looking.
PK Oldvalue NewValue
1 Brown Blonde
Many thanks for looking.
You can use inserted and deleted tables which are available in triggers. Inserted table contains the new values of the modified records and deleted table contains the old values (deleted or updated). I was wondering if you are going to detect any changes or just in a single column? If you are going to detect all changes in a table, use the proper data type for columns in audit table (sql_variant fits here) or you should have some different structure: PK, col1_old_value, col1_new_value, col2_old_value, col2_new_value,....
ASKER
Many thanks for your answer - sorry could you explain a bit more please!? I'm really new to all this - what do you mean by o.PK, i.pk, o.value, and then innerjoin urtable o????? I do undertsnad SQL but I'm not sure what I should be putting instead of the O, i.pk etc!! Thanks again.
O and I are aliases given for 'urTable' and 'deleted' tables respectively;
in order to make the above code work, just replace all the occurences 'urTable' with your original tbale name
in order to make the above code work, just replace all the occurences 'urTable' with your original tbale name
ASKER
I will be auditing all columns - sql variant fits here??????????
ASKER
aneeshattingal: - thanks once again. I see what you mean regarding aliases. Now when I try to create the trigger, it's saying Invalid column name 'value' - any further advice? Thanks again.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ignore that last comment - I worked it out - many thanks for your help.
create trigger tr_u_audit
ON urTable
FOR UPDATE
as
SELECT o.Pk, o.Value NewValue , i.value OldValue
FROM deleted i
INNER JOIN urTable o ON o.Pk = i.Pk
Aneesh R