I have 2 tables one called equipment and one called stocklevel i have a trigger on the equipment table which is below so when i insert equipment in the equipment table it inserts the type in the stock level and a quantity of 1.
create trigger insertequip on [dbo].[equipment]
insert into stocklevel (equiptype,quantity)
(select Producttype,1 from inserted)
on the stock level table i have another insert trigger which i have attached which is supposed to insert any equip types if they are not in the table with the quantity of 1 as thay are added.
If the equip type is allready in the stock level table then it would only update the quantity field to +1.
What is happenening though is when i insert equipment into the equipment table for the first time it inserts it into the stock level table with a quantity of 2 and also if the equip type exists in the stock level table it will update the quantity but also put in the type again on another row with the quantity of 2.
alter TRIGGER Stockinsert ON [dbo].[StockLevel]
declare @EquipType Varchar(50)
declare @Equiptype2 Varchar(50)
select @Equiptype = equiptype from inserted
if exists (select equiptype from stocklevel where equiptype=@equiptype)
update stocklevel set quantity=quantity +1 where Equiptype=@Equiptype
print 'Updated stock level for' + @Equiptype
update stocklevel set equiptype=@equiptype,quantity = 1
print 'New Equipment Added'