ALTER TRIGGER [DeletefromOrderRebate2] ON [dbo].[OrderRebate] 
after insert
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	--SET NOCOUNT ON;

    -- Insert statements for trigger here


    -- Insert statements for trigger here
delete orderrebate
FROM OrderRebate inner join inserted on inserted.ord_type = OrderRebate.ord_type and
inserted.ord_no = OrderRebate.ord_no and inserted.line_seq_no = OrderRebate.line_seq_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no
and inserted.line_seq_no = OrderRebate.line_seq_no and inserted.cd_tp = '3'
END
--GO

delete t
FROM 
(
	select ord_type, ord_no, item_no, line_seq_no from OrderRebate
	group by ord_type, ord_no, item_no, line_seq_no 
	having count(*)>1
) as t
inner join inserted on inserted.ord_type = t.ord_type and
inserted.ord_no = t.ord_no and inserted.line_seq_no = t.line_seq_no
where inserted.ord_type = t.ord_type and inserted.ord_no = t.ord_no
and inserted.line_seq_no = t.line_seq_no and inserted.cd_tp = '3'

quote:

---

Originally posted by Vack

Records being inserted into OrderRebate

Ord_type Ord_no Item_no line_seq_no cd_tp
O 55 part2 1 1
O 55 part2 1 3
O 56 part3 1 3

Since order 55 had two records where the first 4 fields are identical I want the record with cd_tp 3 deleted. But I do not want to delete ord_no 56 which has a cd_tp of 3

---

delete OrderRebate
FROM OrderRebate
inner join inserted on inserted.ord_type = OrderRebate.ord_type and
inserted.ord_no = OrderRebate.ord_no and inserted.line_seq_no = OrderRebate.line_seq_no
where inserted.ord_type = OrderRebate.ord_type and inserted.ord_no = OrderRebate.ord_no
and inserted.line_seq_no = OrderRebate.line_seq_no and inserted.cd_tp = '3'
and exists 
(
	select ord_type, ord_no, item_no, line_seq_no 
	from OrderRebate
	where 
		inserted.ord_type = ord_type and
		inserted.ord_no = item_no and
		inserted.ord_no = ord_no and inserted.line_seq_no = line_seq_no
	group by ord_type, ord_no, item_no, line_seq_no 
	having count(*)>1
) 

delete OrderRebate
FROM OrderRebate
where exists 
(
	select ord_type, ord_no, item_no, line_seq_no 
	from OrderRebate
	where 
		inserted.ord_type = ord_type and
		inserted.ord_no = item_no and
		inserted.ord_no = ord_no and inserted.line_seq_no = line_seq_no and inserted.cd_tp = '3'

	group by ord_type, ord_no, item_no, line_seq_no 
	having count(*)>1
) 

quote:

---

Originally posted by Vack

To answer tkizer:

Wouldn't I run into the same problem with my insert?
It would find two records in a rebate table and want to write them both.

---