0

I want to create a trigger that runs just before a row in a table is updated, and writes all the fields in the row before it is updated to an archive table. What would be the correct syntax required to gain access to the row fields before the update so that I can write them into my archive table?

EDIT :

So this should do what I want, but it doesn't seem to work. I get the error 'there is already an object called config_SystemSettings in the database :

CREATE TRIGGER [config].[UpdateSystemSettings]

ON [config].[SystemSetting] AFTER UPDATE AS BEGIN

SET NOCOUNT ON;

SELECT old.settingId, old.campId, old.settingKey, old.settingValue
into [history].[config_SystemSettings]
FROM [config].[SystemSetting] AS old
INNER JOIN deleted AS del ON del.settingId = old.settingId

END GO

user517406
  • 13,623
  • 29
  • 80
  • 120
  • possible duplicate of [How can I do a BEFORE UPDATED trigger with sql server?](http://stackoverflow.com/questions/642822/how-can-i-do-a-before-updated-trigger-with-sql-server) – Chris J Apr 13 '12 at 08:28

1 Answers1

0

SELECT ... INTO always wants to create a new table - so use INSERT ... SELECT instead:

CREATE TRIGGER [config].[UpdateSystemSettings]

ON [config].[SystemSetting] AFTER UPDATE AS BEGIN
SET NOCOUNT ON;

insert into [history].[config_SystemSettings] (settingId,campId,settingKey,settingValue)
SELECT old.settingId, old.campId, old.settingKey, old.settingValue
FROM [config].[SystemSetting] AS old
INNER JOIN deleted AS del ON del.settingId = old.settingId

But you will have to explicitly create [history].[config_SystemSettings] first.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • This writes to the table but seems to write the new value (after updating) rather than the old value. I thought the 'deleted' table holds the old values? – user517406 Apr 13 '12 at 09:12
  • Sorry, realised I had to reference del.SettingId etc instead of old.SettingId....fixed – user517406 Apr 13 '12 at 09:14
  • @user517406 - indeed, looking at the rest of your query again, not sure why you're joining to `SystemSetting` at all - surely `deleted` contains all of the information you need. – Damien_The_Unbeliever Apr 13 '12 at 09:20