Trigger using if statement

[Ofina]

I have a table containing orders. I'm trying to create a trigger (SQL 2000) that populates another table with the order number when an order is created with tax=0.

I have attempted two different ways. One results in every order with tax=0 in the table getting inserted into the second table instead of just the new one. The other results in nothing inserted into the second table.

What would be the coding to help me?

 

[markros]

create trigger tr_OrderInsert
on Orders AFTER INSERT
As
begin
  if @@ROWCOUNT = 0
    return
  
   insert into NoTaxes (OrderID, OrderDate)
   select OrderID, OrderDate from Inserted where Tax = 0
END

PluralSight Learning Library

 

[Ofina]

To be more specific and taking your sample trigger into account, what is wrong with this (class_2id and order_type are both in the oe_hdr table):

ALTER TRIGGER [fk_t_fob_and_tax]
ON [oe_hdr]
after INSERT
AS

BEGIN

SET NOCOUNT ON;

if @@ROWCOUNT = 0
return

insert into fk_fob_and_tax (order_num)
select inserted.order_no from inserted
left outer join oe_hdr_tax on inserted.order_no = oe_hdr_tax.order_no
where class_2id like 'fob%'
and (oe_hdr_tax.taxable='y')
and (order_type is null or order_type <> 1877)

END

 

[markros]

Use INNER JOIN. In your case LEFT JOIN behaves as it's an INNER JOIN anyway.

Check

http://beyondrelational.com/blogs/n...-t-bring-all-records-from-the-left-table.aspx

PluralSight Learning Library

 

[SQLSister]

if you need to preserve the left join

Insert into fk_fob_and_tax (order_num)
select inserted.order_no 
from inserted
left outer join oe_hdr_tax on inserted.order_no = oe_hdr_tax.order_no and (oe_hdr_tax.taxable='y')
where class_2id like 'fob%'and (order_type is null or order_type <> 1877)

Puting a where condition on the table on the right side of a left join converts it to an inner join, adding the filtering condition to the join is what you want if you want to preserve the join.

I find that in writing triggers it is easier to debug them if I start with a temp table named #inserted and add records as they would appear in a real insert and then write the code (not the create trigger stuff, the code inside the trigger) using #inserted instead of inserted lets me play with the results to see waht si actually happening before I change it back to inserted and stick it in a trigger.

"NOTHING is more important in a database than integrity." ESquared