SQL Server Performance Forum – Threads Archive

Problems with trigger.

I created a trigger that will insert a value into another table (inv_stock). If the insert that triggered the trigger has ‘multiple_sizes’ then it will load other table (inv_stock) with values from the inv_sizes table. I have debugged this in Query Analyzer and it works there, but when I run it from my IIS application the first two rows are inserted into the inv_stock table and I get an error page. There are 4 rows in the inv_sizes table. Can anyone see anything wrong with this trigger? CREATE TRIGGER [inv_mast_add] ON [dbo].[inv_mast]
FOR INSERT
AS DECLARE @item char(25)
DECLARE @multiple_sizes int
DECLARE c1 CURSOR FOR SELECT item_cd, mulitple_sizes
FROM inv_mast
WHERE item_id = (SELECT MAX(item_id) FROM inv_mast) OPEN c1
FETCH NEXT FROM c1
INTO @item, @multiple_sizes IF ( @multiple_sizes = 1 ) BEGIN
DECLARE @size_cd varchar(12)
DECLARE s1 CURSOR FOR SELECT size_cd
FROM inv_sizes OPEN s1
FETCH NEXT FROM s1
INTO @size_cd WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN
INSERT INTO inv_stock(item_cd, item_size)
VALUES(@item, @size_cd)
COMMIT
END FETCH NEXT FROM s1
INTO @size_cd END CLOSE s1
DEALLOCATE s1
END
ELSE BEGIN
INSERT INTO inv_stock(item_cd)
VALUES(@item)
COMMIT
END
CLOSE c1
DEALLOCATE c1
Thank you in advance,
Andy

---

You need to re write the whole trigger. First of all it has to be declare @item_cd int,@multiple_sizes bit SELECT @item_cd=item_cd,@multiple_sizes = mulitple_sizes
FROM inserted if (@multiple_sizes=1)
begin
INSERT INTO inv_stock(item_cd, item_size)
select @item_cd,size_cd FROM inv_sizes
end
else
begin
INSERT INTO inv_stock(item_cd)
VALUES(@item_cd)
end

---

can you please post it error message.. SURYA

---

]]>