Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Triggers and the Inserted table(MSSQL 7.0)

Posted on 2000-02-25
5
Medium Priority
?
286 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 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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

972 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