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.
fuerteventuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
Hello fuerteventura,

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
0
brejkCommented:
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,....
0
fuerteventuraAuthor Commented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Aneesh RetnakaranDatabase AdministratorCommented:
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
0
fuerteventuraAuthor Commented:
I will be auditing all columns - sql variant fits here??????????
0
fuerteventuraAuthor Commented:
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.
0
Aneesh RetnakaranDatabase AdministratorCommented:
you need to replace 'Value' with the column name which you are tracking ;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fuerteventuraAuthor Commented:
Ignore that last comment - I worked it out - many thanks for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.