compare 2 records, saved using a trigger

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 2008 Forums

Transact-SQL (2008)

compare 2 records, saved using a trigger

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-07 : 11:38:44

hi everyone

i have a table called "orders_office"

CREATE TABLE [dbo].[orders_office](
[idorder] [int] IDENTITY(1,1) NOT NULL,
[office] [varchar](50) NULL,
[client] [varchar](50) NULL
) ON [PRIMARY]

also i have another table to save the changes made to my table
"orders_office" using a trigger ,

the table to storage the updates is called "changes_order_office"

CREATE TABLE [dbo].[changes_order_office](
[idchange] [int] IDENTITY(1,1) NOT NULL,
[idorder] [int] NOT NULL,
[office] [varchar](50) NULL,
[client] [varchar](50) NULL,
[date_change] [datetime] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[changes_order_office] ADD CONSTRAINT [DF_changes_order_office_facha_change] DEFAULT (getdate()) FOR [facha_change]
GO

and my trigger is this

create TRIGGER [dbo].[postUPDATE_order_office]
ON [dbo].[orders_office]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
INSERT into dbo.changes_order_office
([idorder]
,[office]
,[client])

SELECT [idorder]
,[office]
,[client]

FROM deleted
end
GO

lets put some data on my table

INSERT INTO [Pruebas].[dbo].[orders_office]
([office]
,[client])
VALUES
('miami'
,'paul')
GO

now lets update this record 3 times

first one

UPDATE [dbo].[orders_office]
SET [office] = 'new york'
,[client] = 'john'
where idorder =1

second

UPDATE [dbo].[orders_office]
SET [office] = 'phoenix'
,[client] = 'joseph'
where idorder =1

third

UPDATE [dbo].[orders_office]
SET [office] = 'los angeles'
,[client] = 'thomas'
where idorder =1

after all the updates my tables have this info

"orders_office"
1 los angeles thomas

and
"changes_order_office"

1 1 miami paul 2012-05-07 10:20:12.050
2 1 new york john 2012-05-07 10:21:23.653
3 1 phoenix joseph 2012-05-07 10:23:14.340

how could i track all the changes (comparing the records to identifies all the changes)

i think is a little bit tricky because i need to compare the record on my table orders_office (where idorder=1) with (the max idchange (it will be 3)) where idorder=1 on my table changes_order_office

but also i have to compare the max(idchange) =3 in my table changes_order_office with the less idchange before (in this case idchange=2) where idorder=1 in my table changes_order_office and etc

im confused,
i have no point to start

any help will be really appreciate

many many thanks in advanced

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-05-07 : 14:48:08

Please post the desired output for these comparisons (based on your sample data).

Be One with the Optimizer
TG

sebastian11c
Posting Yak Master

129 Posts

Posted - 2012-05-07 : 15:07:30

for example i need a query to compare the records with IDORDER=1 with the record inmediatly before with IDORDER=1

it could be the idchange=3 (with idorder=1) compare with the inmediatly record before where idorder=1 in this case the record with idchange=2, also compare idchange =2 with idchange =1, and etc

and ill get something like this

idchange idorder office client

3 1 changed changed
2 1 changed changed

many thanks in advanced

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-05-07 : 15:10:35

Looks like this is being handled by your other post?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=174412

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources