Update Trigger - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update Trigger

Author

Topic

-blackfish-
Starting Member

2 Posts

Posted - 2009-04-01 : 08:35:54

I know that triggers have been discussed any number of times on here as I've done searches. I can't seem to find one that relates specifically to what I'm after, so here goes:

I created a trigger on a table that would write certain values to an audit table each time there was a change to data. It works fine, except for one small detail; it writes the updated value of the data.

For example, if I update a surname from 'SMITH' to 'SMYTH', what gets written to the audit table is 'SMYTH', the updated value. I would like to retain the original value, 'SMITH' and write that to the audit table. Is this possible?

TIA for any help.

Here's what I'm using:

CREATE TRIGGER [dbo].[TRtblPatientsUpdate]

   ON  [dbo].[tblpatients]

   after UPDATE

AS

BEGIN
 
 
 SET NOCOUNT ON;

    INSERT INTO tblauditpatients

(tblpatientspatid, tblpatientsdob, 
tblpatientssuranme, tblpatientsforename)
    
SELECT patid, dob, surname, forename
 
FROM inserted

END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-01 : 08:37:22

FROM ~~inserted~~ deleted

E 12°55'05.63"
N 56°04'39.26"

-blackfish-
Starting Member

2 Posts

Posted - 2009-04-01 : 08:56:14

Doh!

Thanks very much peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources