help with writting a trigger

Hi,
I have 2 tables ''Data' and 'LatestData' with same fields:
- ID,
- TruckNo,
- DateTime,
- Comment.
My application adds data to the Data table. I would like to have a trigger
which automatically updates or adds (if not exists) newly added records into
LatestData table. This trigger should also look at DateTime, and add records
only if they are newer then existing for a TruckNo.
So LatestData table contains only one and newest record per one TruckNo.
Thanks for help.
PrzemoHi
(untesed)
IF NOT EXISTS
(SELECT * FROM LatestData WHERE LatestData.TruckNo=inserted.TruckNo)
INSERT INTO LatestData SELECT * FROM Inserted
IF NOT EXISTS
(SELECT * FROM LatestData WHERE LatestData.ID=inserted.ID)
INSERT INTO LatestData SELECT * FROM Inserted
ELSE
UPDATE LatestData SET col=(SELECT i.col FROM inserted i JOIN deleted d
ON d.ID=i.ID)
WHERE EXISTS
(SELECT * FROM LatestData WHERE LatestData.ID=deleted.ID)
"Przemo" <Przemo@.discussions.microsoft.com> wrote in message
news:85F7771F-96E1-42FB-9B2D-F8EC02BCCED1@.microsoft.com...
> Hi,
> I have 2 tables ''Data' and 'LatestData' with same fields:
> - ID,
> - TruckNo,
> - DateTime,
> - Comment.
> My application adds data to the Data table. I would like to have a trigger
> which automatically updates or adds (if not exists) newly added records
> into
> LatestData table. This trigger should also look at DateTime, and add
> records
> only if they are newer then existing for a TruckNo.
> So LatestData table contains only one and newest record per one TruckNo.
> Thanks for help.
> Przemo|||CREATE TRIGGER [TR1] ON [dbo].[Data]
FOR INSERT, UPDATE, DELETE
AS
IF EXISTS
(SELECT * FROM LatestData,Inserted WHERE LatestData.ID=inserted.ID)
DELETE FROM LatestData WHERE ID IN( SELECT INSERTED.ID FROM
LatestData,Inserted WHERE LatestData.ID=inserted.ID)
INSERT INTO LatestData SELECT * FROM Inserted
---
Hi Uri,
I used your logic only, thanks for that but thought not to mess up with
update. I tested your code but it failed the update part and tehre was few
syntax error also.
The above logic delete updated record from LatestData so we consider it as a
new Insert and not to mess with update.
Thanks,
Sree
"Uri Dimant" wrote:

> Hi
>
> (untesed)
>
> IF NOT EXISTS
> (SELECT * FROM LatestData WHERE LatestData.TruckNo=inserted.TruckNo)
> INSERT INTO LatestData SELECT * FROM Inserted
>
> IF NOT EXISTS
> (SELECT * FROM LatestData WHERE LatestData.ID=inserted.ID)
> INSERT INTO LatestData SELECT * FROM Inserted
> ELSE
> UPDATE LatestData SET col=(SELECT i.col FROM inserted i JOIN deleted d
> ON d.ID=i.ID)
> WHERE EXISTS
> (SELECT * FROM LatestData WHERE LatestData.ID=deleted.ID)
>
>
> "Przemo" <Przemo@.discussions.microsoft.com> wrote in message
> news:85F7771F-96E1-42FB-9B2D-F8EC02BCCED1@.microsoft.com...
>
>