We help IT Professionals succeed at work.

Trigger problem

160 Views
Last Modified: 2010-04-21
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

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'

Open in new window

Comment
Watch Question

Commented:

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,quantity = 1;
print 'New Equipment Added'
end

Commented:
You should use an INSTEAD OF trigger



Author

Commented:
yea but when i used that it just did not put anything in the stock level table
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.