Solved

SQL trigger using loop for batch processing

Posted on 2011-03-15
3
828 Views
Last Modified: 2012-05-11
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
Comment
Question by:Littleghostface
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 7

Accepted Solution

by:
lozzamoore earned 125 total points
ID: 35139033
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
 
LVL 70

Expert Comment

by:Qlemo
ID: 36032402
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question