|
sebastian11c
Posting Yak Master
125 Posts |
 Posted - 05/07/2012 : 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
|
|
|
sebastian11c
Posting Yak Master
125 Posts |
Posted - 05/07/2012 : 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 |
 |
|
compare 2 records, saved using a trigger
