Most recent transaction

[prkr8]

A trigger which inserts into table A from 2 tables B and C.
TableA data looks like
1001 38488 JAMES King 2006-11-29 07:56:45.000
2002 38488 JAMES King 2006-07-18 08:23:59.000
3003 38488 JAMES King 2006-08-14 09:22:14.000
Here in TableA i want delete rows except most recent row 1001 --.
Need to include the delete query after insert rows in below trigger.pls advice.Thanks.

CREATE TRIGGER [trig1] ON tableB
FOR INSERT,DELETE
AS
begin
insert into tableA(item_num,emp_id,emp_name,tran_date)
select
a.item_num,
a.emp_id,
c.emp_name,
max(a.start_tran_date)
from tableB a,tableC c where
a.emp_id=b.emp_id
group by a.item_num,
a.emp_id,
c.emp_name
end

 

[monksnake]

Couldn't you add the following to the trigger:

delete from a where [i]date column name[/i] = max([i]date column name[/i])

 

[SQLSister]

OK first the existing trigger is wrong, you want to use the inserted psuedotable instead of table b to grab only the records which were inserted. Also you should not use those old style joins anywhere becasue they are not supported in future versions of SQL Server. Please convert to using standard joins instead. Not tested suggestion for the delete, do not try on production until you test!

delete table a
where item_num not in (select Item_num from inserted) 
and Emp_id in (select distinct emp_id from Inserted)

There is also a way to do this in a join, I'm sure but I don;t have time to work it out right now.


Questions about posting. See faq183-874

 

[prkr8]

Thanks for your replys

delete table a
where item_num not in (select Item_num from temptable)
and Emp_id in (select distinct emp_id from temptable)


not deleteing non of the records from tableA.

I have to see only one record for each emp_id means most recent transaction.pls advice.

 

[prkr8]

sorry small correction

1001 38488 JAMES King 2006-11-29 07:56:45.000
1001 38488 JAMES King 2006-07-18 08:23:59.000
1001 38488 JAMES King 2006-08-14 09:22:14.000

item_num is same for all txns.