Storing old values after UPDATE

[kfdiw]

When I update a field f1 I should store its previous value in the field f2. How can I achieve this?

[Gregory]

1) you can use triggers
for example:update trigger (will be used only for update)

create trigger <trigger_name> on <table_name>
for update
as
if update (field1)
/* that will make sure that trigger is used only if field1 si being changed */
update <table_name> set field2 = deleted.field1
where <where clause to make sure only row that needs to be updated is changed>

-or-
2. in update statement (w/o using triggers):
update <table_name>
set field2 = field1,
field1 = <new value>

-or-
3. create simple stored procedure, that will take new value for field1 as a parameter


------------
kfdiw at 3/15/99 11:13:25 AM

When I update a field f1 I should store its previous value in the field f2. How can I achieve this?