• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 838
  • Last Modified:

SQL trigger using loop for batch processing

With some help, I've created a conditional trigger that handles a fairly simple update. The issue is that I needed a loop to handle batch processing when there were multiple records inserted. I have gotten some help on the loop syntax, but I am having issues with my declare statements. Can anyone see the issue here. I am new to this. Below are the error messages and the trigger.

Errors:
Msg 102, Level 15, State 1, Procedure MAILSTOP_UPDATE, Line 37
Incorrect syntax near ';'.
Msg 137, Level 15, State 2, Procedure MAILSTOP_UPDATE, Line 44
Must declare the scalar variable "@roomID".
Msg 137, Level 15, State 2, Procedure MAILSTOP_UPDATE, Line 60
Must declare the scalar variable "@roomID".

Trigger:
ALTER TRIGGER [dbo].[MAILSTOP_UPDATE]
   ON  [dbo].[TIAAMH]
   AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--check if single insert do the update
IF (SELECT COUNT(*) FROM INSERTED) = 1
BEGIN -- check batch
      IF EXISTS (SELECT * FROM INSERTED WHERE INSERTED.TORMID LIKE 'ZAWS%')
      BEGIN
            UPDATE TIAAN0
            SET MSTOP = TIAAB0.AWS_MSTOP
            FROM TIAAB0 INNER JOIN INSERTED I ON TIAAB0.BLDGCODE = SUBSTRING(I.TORMID,1,4)
      END

      IF NOT EXISTS (SELECT * FROM INSERTED WHERE INSERTED.TORMID LIKE 'ZAWS%')
      BEGIN
            UPDATE TIAAN0
            SET MSTOP = TIAAA0.MAILSTOP
            FROM TIAAA0 INNER JOIN INSERTED I ON TIAAA0.BLDGCODE+TIAAA0.RMID = I.TORMID
      END
END -- check batch

--check batch here and update one at a time
IF (SELECT COUNT(*) FROM INSERTED) > 1
BEGIN

DECLARE @roomID;

DECLARE db_cursor CURSOR FOR

SELECT  substring(I.TORMID, 1, 4) FROM  INSERTED I

OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @roomID;

WHILE @@FETCH_STATUS = 0  
BEGIN
      IF EXISTS (SELECT * FROM INSERTED WHERE INSERTED.TORMID LIKE 'ZAWS%')
      BEGIN
            UPDATE TIAAN0
            SET MSTOP = TIAAB0.AWS_MSTOP
            FROM TIAAB0 INNER JOIN INSERTED I ON TIAAB0.BLDGCODE = SUBSTRING(I.TORMID,1,4)
      END
      IF NOT EXISTS (SELECT * FROM INSERTED WHERE INSERTED.TORMID LIKE 'ZAWS%')
      BEGIN
            UPDATE TIAAN0
            SET MSTOP = TIAAA0.MAILSTOP
            FROM TIAAA0 INNER JOIN INSERTED I ON TIAAA0.BLDGCODE+TIAAA0.RMID = I.TORMID
      END
FETCH NEXT FROM db_cursor INTO @roomID
END  

CLOSE db_cursor  
DEALLOCATE db_cursor

END -- end loop

END


0
Littleghostface
Asked:
Littleghostface
1 Solution
 
lozzamooreCommented:
You shouldn't need to mess about with cursors here.

Instead, it should be doable in one "hit" using something like this:

UPDATE TIAAN0
SET MSTOP = CASE WHEN INSERTED.TORMID LIKE 'ZAWS%' THEN t1.AWS_MSTOP ELSE t2.MAILSTOP END
FROM INSERTED i
LEFT OUTER JOIN TIAAB0 t1 ON t1.BLDGCODE = SUBSTRING(I.TORMID,1,4)
LEFT OUTER JOIN TIAAA0 t2 ON t2.BLDGCODE+TIAAA0.RMID=I.TORMID

Regards,
0
 
QlemoC++ DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now