Solved

SQL trigger using loop for batch processing

Posted on 2011-03-15
3
824 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
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 68

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now