how to identify a column update

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

General SQL Server Forums

New to SQL Server Programming

how to identify a column update

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-09 : 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

1537 Posts

Posted - 2012-04-09 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-09 : 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

129 Posts

Posted - 2012-04-09 : 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

129 Posts

Posted - 2012-04-09 : 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

52326 Posts

Posted - 2012-04-09 : 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

1537 Posts

Posted - 2012-04-09 : 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

129 Posts

Posted - 2012-04-09 : 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

1537 Posts

Posted - 2012-04-09 : 16:52:53

Ok I have an Idea.

Please post your Trigger.

Jim
Users <> Logic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-04-12 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources