[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL trigger using loop for batch processing

Posted on 2011-03-15
3
Medium Priority
?
834 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 500 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 71

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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

650 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