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
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
ASKER
Mitek...
No aversions to cursors here. Just a boss that expressed a concern about rowlocks when using a cursor in a trigger.
No aversions to cursors here. Just a boss that expressed a concern about rowlocks when using a cursor in a trigger.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
row locks on INSERTED table? who else can access it simultaniously?
just wondering what's the problem here.
</wqw>
just wondering what's the problem here.
</wqw>
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.
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.
also, why don't you like cursors? do you have some particular aversion to them ?