Solved

Triggers and the Inserted table(MSSQL 7.0)

Posted on 2000-02-25
5
256 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
  • 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 60 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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