Mearsy1976
asked on
Trigger problem
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]
for insert
as
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.
please help
create trigger insertequip on [dbo].[equipment]
for insert
as
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.
please help
alter TRIGGER Stockinsert ON [dbo].[StockLevel]
For INSERT
AS
declare @EquipType Varchar(50)
declare @Equiptype2 Varchar(50)
select @Equiptype = equiptype from inserted
if exists (select equiptype from stocklevel where equiptype=@equiptype)
begin
update stocklevel set quantity=quantity +1 where Equiptype=@Equiptype
print 'Updated stock level for' + @Equiptype
end
Else
update stocklevel set equiptype=@equiptype,quantity = 1
print 'New Equipment Added'
You should use an INSTEAD OF trigger
ASKER
yea but when i used that it just did not put anything in the stock level table
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thanks
alter TRIGGER Stockinsert ON [dbo].[StockLevel]
For INSERT
AS
declare @EquipType Varchar(50);
declare @Equiptype2 Varchar(50);
set @Equiptype = ( select equiptype from inserted);
if exists (select equiptype from stocklevel where equiptype=@equiptype)
begin
update stocklevel set quantity=quantity +1 where Equiptype=@Equiptype;
print 'Updated stock level for' + @Equiptype
end
Else
begin
update stocklevel set equiptype=@equiptype,quant
print 'New Equipment Added'
end