Link to home
Start Free TrialLog in
Avatar of MicroD
MicroD

asked on

Triggers and the Inserted table(MSSQL 7.0)

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









 
Avatar of mitek
mitek

for all i know, you cannot do that.

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

ASKER

Mitek...
No aversions to cursors here. Just a boss that expressed a concern about rowlocks when using a cursor in a trigger.
ASKER CERTIFIED SOLUTION
Avatar of mitek
mitek

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
row locks on INSERTED table? who else can access it simultaniously?

just wondering what's the problem here.

</wqw>
Avatar of MicroD

ASKER

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.