Solved

Triggers and the Inserted table(MSSQL 7.0)

Posted on 2000-02-25
5
263 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

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

861 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

25 Experts available now in Live!

Get 1:1 Help Now