Solved

SQL trigger using loop for batch processing

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

17 Experts available now in Live!

Get 1:1 Help Now