June 17, 2005 at 1:45 pm
Is there a way to mimic Oracle's FOR EACH ROW functionality in SQL Server? I have a DTS that inserts multiple rows and the trigger only gets fired once, but I want it to get fired for each row inserted.
June 17, 2005 at 3:07 pm
Here's how I did it using a cursor..
CREATE TRIGGER [SendErrorNotification] ON dbo.ClickonError
AFTER INSERT
AS
DECLARE @edit varchar(15)
DECLARE cedit CURSOR FOR
SELECT EditNumber
FROM INSERTED
OPEN cedit
FETCH NEXT FROM cedit
INTO @edit
WHILE @@FETCH_STATUS = 0
BEGIN
--add the date stamp and send the error notification
UPDATE dbo.ClickonError
SET DateStamp = GetDate()
WHERE dbo.ClickonError.EditNumber = @edit
EXEC dbo.s_SendErrorNotification @edit
FETCH NEXT FROM cedit
INTO @edit
END
CLOSE cedit
DEALLOCATE cedit
June 22, 2005 at 6:48 am
No. SQL Triggers does not have a FOR EACH ROW. Depending on what dbo.s_SendErrorNotification is doing, maybe you could solve it without a cursor though...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply