Audit 3 fields in one table

Hello,
I am unfamiliar with SQL Server 2005 but want to learn more.
I have an application with a MS Access 2003 Front end and a 2005 SQL
Server backend.
I have a table called HandoverClinicalChanges, there are 3 main fields
which are updated by Doctors - usually by adding to the end of the
previous entry, sometimes by deleting the previous entry and adding
their own.
I want to be able to capture the changes and additionsthey record to
these fields in a table (? audit table).
Is there an easy way do this in SQL SERVER 2005 or should I do it in
ACCESS FE?
Thank you for any help and advice.
Regards
KevHello,
Take a look into the INSERT/DELETE Triggers in books online. So when ever
there is a delete or Insert happendson table HandoverClinicalChanges a
record
will be inserted to the audiit table. See the below URL for examples and
usages of triggers.
http://www.sql-server-performance.com/nn_triggers.asp
Thanks
Hari
"Kev" <kevin.vaughan@.nhw.hume.org.au> wrote in message
news:1172208080.462256.162750@.p10g2000cwp.googlegroups.com...
> Hello,
> I am unfamiliar with SQL Server 2005 but want to learn more.
> I have an application with a MS Access 2003 Front end and a 2005 SQL
> Server backend.
> I have a table called HandoverClinicalChanges, there are 3 main fields
> which are updated by Doctors - usually by adding to the end of the
> previous entry, sometimes by deleting the previous entry and adding
> their own.
> I want to be able to capture the changes and additionsthey record to
> these fields in a table (? audit table).
> Is there an easy way do this in SQL SERVER 2005 or should I do it in
> ACCESS FE?
> Thank you for any help and advice.
> Regards
> Kev
>|||Hello Hari and everyone else,
Thanks for your response.
So far I've got the below code working to an extent:
The problem with it is that it inserts the entire cell contents (old
and new data) of the datasheet cell, not just the new data which was
typed by the user. Is this possible to do?
Regards
Kevin
CREATE TRIGGER tgClinicalInstAudit_Trigger
ON kvaugha.dbo_ClinicalInstances
AFTER UPDATE
AS
IF UPDATE ([MedicalProblems]) OR UPDATE ([MedicalPlan]) OR UPDATE
([ActionsTaken])
BEGIN
INSERT INTO ClinicalHandoverAudit
([MedProbs]
,[MedPlan]
,[ActsTaken]
,[AudDateTime])
SELECT
inserted.[MedicalProblems]
,inserted.[MedicalPlan]
,inserted.[ActionsTaken]
,GETDATE()
FROM inserted;
end|||Kev (kevin.vaughan@.nhw.hume.org.au) writes:
> Hello Hari and everyone else,
> Thanks for your response.
> So far I've got the below code working to an extent:
> The problem with it is that it inserts the entire cell contents (old
> and new data) of the datasheet cell, not just the new data which was
> typed by the user. Is this possible to do?
So if for instance MedProbs initially read: "This patient is sick", and the
doctor then adds. "And he will never get well", you only want "And he will
never get well" to be added to the audit table?
If think this would be quite difficult, not the least since you say
that dcctors also can modify already exinst text. What if the doctor
changes the text to "This patient is very sick". Should the trigger
just log "very"?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx