Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Triggers and the Inserted table(MSSQL 7.0)

Posted on 2000-02-25
5
Medium Priority
?
282 Views
Last Modified: 2008-03-06
I have written an insert trigger that selects the inserted rows into variables and passes them to a stored procedure.Using a cursor I can pass a multiple row insert to the stored procedure using an @@Fetch_Status loop.
  I'd like to do the same thing without using a cursor. But the best I can do is to pass the last row inserted.In Sybase we could use 'For Each Row'.How can I accomplish this in MSSQL?
  Here is the trigger using a cursor.

CREATE TRIGGER T_AFTERCHAREGINSERT ON [CHARGES]
FOR INSERT

AS

DECLARE @CHGID INTEGER,
            @ACCTID CHAR(8),
            @PATID INTEGER,
            @PROVIDERID CHAR(6),
            @OFFICEID CHAR(6),
            @FACILITYID INTEGER,
            @REFERRALID INTEGER,
            @PREESTIMATE INTEGER,
            @SUBMETHOD CHAR(1),
            @PRACTICEID INTEGER,
            @UBID INTEGER,
            @PROCCODE CHAR(6)


DECLARE INS_CURSOR CURSOR FOR

SELECT N_CH_ID,
      C_PE_ACCOUNT,
      N_PE_ID,
      C_ST_ID,
      C_OF_ID,
      C_FA_ID,
      N_RE_ID,
      L_CH_IS_PREESTIMATE,
      C_CH_SUBMETHOD,
      N_PR_ID,
      N_UB_ID,
      C_PD_ID

FROM INSERTED

OPEN INS_CURSOR

FETCH NEXT FROM INS_CURSOR

      INTO @CHGID, @ACCTID, @PATID, @PROVIDERID, @OFFICEID, @FACILITYID,@REFERRALID, @PREESTIMATE,@SUBMETHOD, @PRACTICEID, @UBID, @PROCCODE

WHILE @@FETCH_STATUS = 0

BEGIN

      EXECUTE  WM_DISTRIBUTECHARGE @CHGID, @ACCTID, @PATID, @PROVIDERID, @OFFICEID, @FACILITYID,@REFERRALID, @PREESTIMATE,@SUBMETHOD, @PRACTICEID, @UBID, @PROCCODE


      FETCH NEXT FROM INS_CURSOR

      INTO @CHGID, @ACCTID, @PATID, @PROVIDERID, @OFFICEID, @FACILITYID,@REFERRALID, @PREESTIMATE,@SUBMETHOD, @PRACTICEID, @UBID, @PROCCODE


      
END

CLOSE INS_CURSOR

DEALLOCATE INS_CURSOR









 
0
Comment
Question by:MicroD
[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
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:mitek
ID: 2558353
for all i know, you cannot do that.

also, why don't you like cursors? do you have some particular aversion to them ?
0
 

Author Comment

by:MicroD
ID: 2558380
Mitek...
No aversions to cursors here. Just a boss that expressed a concern about rowlocks when using a cursor in a trigger.
0
 
LVL 4

Accepted Solution

by:
mitek earned 180 total points
ID: 2558467
yet using a cursor in a trigger is the standard way to provide row-level attention to the modified results.

i haven't heard of row triggers for sql server (did anyone?)

but -- who knows, maybe there is a hack somewhere that will do that. i would use cursor though and consider this to be the only reliable way.

-- mitek
0
 
LVL 4

Expert Comment

by:wqw
ID: 2560893
row locks on INSERTED table? who else can access it simultaniously?

just wondering what's the problem here.

</wqw>
0
 

Author Comment

by:MicroD
ID: 2572535
I think you guys have convinced me that this won't be a problem. It makes sense that only the instance performing the insert should have access to the inserted table for that instance anyway.And if you were able to have multiple instances of the inserted table at the same time you would stand the chance of data corruption. So I'll tell the Boss to back of the medication and continue to do it the way that makes sense.

Thanks for you input MITEK & WQW. I'm sure how to split points but since both of you did help me make up my own mind I'd still like to give you guys the points. I'd like to do 45 to MITEK and 15 to WQW for the slap in the face to wake me up.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

730 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