| Author |
 Topic  |
|
|
sebastian11c
Posting Yak Master
125 Posts |
 Posted - 04/09/2012 : 12:30:38
|
hi
i have a table called "info" with 20 columns, every deleted row when a user do an update from this table is saved in other table called "infobackup"
now i want to identify wich column was changed from every update because compare all te 20 columns to identify wich one has changed its to hard
any ideas, any query any help will be appreciate
many many thanks in advanced |
|
|
JimL
SQL Slinging Yak Ranger
USA
1530 Posts |
Posted - 04/09/2012 : 12:51:58
|
I take it your using a trigger.
You could add a new column in the backup table with a case statement
Something like
Colchanged = case when inserted.c1<> deleted.c1 then 'c1' when inserted.c2 <> deleted.c2 then 'c2' ..........
Jim
Users <> Logic |
 |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 04/09/2012 : 12:52:38
|
Do you have :
a) a column (such as primary key) in your INFOBACKUP table that can be used to associate the rows to a the row in the INFO table?
and
b) a column (such as a timestamp or id number) in your INFORBACKUP table that can be used to identify the sequence of updates (i.e., to order the rows in the INFORBACKUP table in chronological order)?
If you do, then you can write a query to pick all the updated rows for any given row in the INFO table, order them chronologically and find which columns have changed. |
|
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/09/2012 : 13:13:53
|
thanks for your answer sunitabeck, yes i have a primarykey in my table "infobackup" and also i have a column with a getdate (),in my table "infobackup",, the problem is people can do updates from several columns at the same time,
any idea to identify the columns have changed??
thanks |
|
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/09/2012 : 13:16:16
|
thanks for tyour answer jimL
but people can do updates from several columns at the same time
any idea to solve this
tahnks in advanced |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 04/09/2012 : 14:56:15
|
so multiple column updates you will store all their details inside Colchanged ?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1530 Posts |
Posted - 04/09/2012 : 15:18:02
|
1st are you inserting the record on the other table via a trigger on the original table?
Jim
Users <> Logic |
|
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/09/2012 : 16:48:01
|
yes im using a trigger , i insert all the columns from the table deleted in my table "infobackup" with an identity column and a date column,, but i dont save antything else,,
and i would like to create a query with the details of the columns that have changed |
|
|
|
JimL
SQL Slinging Yak Ranger
USA
1530 Posts |
Posted - 04/09/2012 : 16:52:53
|
Ok I have an Idea.
Please post your Trigger.
Jim
Users <> Logic |
|
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 04/12/2012 : 17:46:27
|
sorry for the delay
this is my trigger, any correction please let me know "the columns are on spanish"
create TRIGGER [dbo].[pre_postUPDATE_info]
ON [dbo].[info]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
INSERT INTO [dbo].[infobackup]
([IdVehiculo]
,[Placa]
,[Matricula]
,[Motor]
,[Serie]
,[Chasis]
,[EsImportado]
,[Manifiesto]
,[IdCiudadNacionalizacion]
,[FechaNacionalizacion]
,[Cilindraje]
,[Modelo]
,[CantidadPuertas]
,[CodigoFasecolda]
,[ValorFasecolda]
,[IdMarcaLinea]
,[IdClase]
,[IdColor]
,[IdServicio]
,[IdCarroceria]
,[IdTramiteDoc]
,[IdOrganismoDoc]
,[FechaExpedicionDoc]
,[Consecutivo]
,[FechaSOAT]
,[FechaRTM]
, valorcomercial
)
select IdVehiculo ,Placa, Matricula, Motor, Serie , Chasis ,
EsImportado ,
Manifiesto ,
IdCiudadNacionalizacion ,
FechaNacionalizacion ,
Cilindraje ,
Modelo ,
CantidadPuertas ,
CodigoFasecolda ,
ValorFasecolda ,
IdMarcaLinea ,
IdClase ,
IdColor ,
IdServicio ,
IdCarroceria ,
IdTramiteDoc ,
IdOrganismoDoc ,
FechaExpedicionDoc ,
Consecutivo ,
FechaSOAT ,
FechaRTM ,
ValorComercial
from deleted
end
thanks in advanced |
|
|
| |
Topic |
|
|
|
how to identify a column update
