error on second insert select within cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

error on second insert select within cursor

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-27 : 14:38:52

When I added the second INSERT SELECT, I get the following error:
Msg 50000, Level 16, State 1, Procedure tu_policy_transaction, Line 203
ERROR: process attempted to update reported policy_transaction
Msg 3609, Level 16, State 1, Procedure tiu_policy_transaction_fee, Line 17
The transaction ended in the trigger. The batch has been aborted.

Sorry it is so long...

begin tran

DECLARE @policy_transaction_temp table (
[Policy_Base] varchar(12)
,[Policy_Suffix] smallint
,[Sequence] int
,[Transaction_Type] char(2)
,[Effective_Date] datetime
,[Installment_Fee] smallmoney
,[Entered_Date] datetime
,[Endorsement] smallint
,[Company] char(1)
,[Subcompany] char(1)
)

INSERT @policy_transaction_temp (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Installment_Fee]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
)
SELECT policy.policy_base --[Policy_Base]
,policy.policy_suffix --[Policy_Suffix]
,(
SELECT MAX(policy_transaction.sequence)
FROM policy_transaction
WHERE policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
) + 1 AS sequence --[Sequence]
,'AD' --[Transaction_Type]
,getdate() --[Effective_Date]
,-10.00 --[Installment_Fee]
,getdate() --[Entered_Date]
,policy.endorsement --[Endorsement]
,policy.company --[Company]
,policy.subcompany --[Subcompany]
FROM policy_transaction
INNER JOIN policy
ON policy_transaction.policy_base = policy.policy_base
AND policy_transaction.policy_suffix = policy.policy_suffix
INNER JOIN policy_transaction_fee
ON policy_transaction.policy_base = policy_transaction_fee.policy_base
AND policy_transaction.policy_suffix = policy_transaction_fee.policy_suffix
WHERE (policy_transaction.entered_date >= '2008-08-28')
AND (policy_transaction.transaction_type IN ('rn','np'))
AND (policy.status <> 'ca')
AND (policy.eft_flag = 'y')
AND (policy.current_flag = 'y')
AND (policy.installment_fees >= 10)
AND (policy_transaction_fee.fee_code = 'ins')
GROUP BY policy.policy_base,
policy.policy_suffix,
policy.endorsement,
policy.company,
policy.subcompany

select * from @policy_transaction_temp

DECLARE @policy_base varchar(12)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT policy_base
FROM @policy_transaction_temp

OPEN c1

FETCH NEXT FROM c1
INTO @policy_base

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT policy_transaction (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Installment_Fee]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
)
SELECT [Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[Transaction_Type]
,[Effective_Date]
,[Installment_Fee]
,[Entered_Date]
,[Endorsement]
,[Company]
,[Subcompany]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

--ADDING THIS CAUSES THE ERROR
INSERT policy_transaction_fee (
[Policy_Base]
,[Policy_Suffix]
,[Sequence]
,[fee_code]
,[fee_amount]
,[entered_by]
,[entered_date]
)
SELECT [Policy_Base]
,[Policy_Suffix]
,(( SELECT MAX(policy_transaction_fee.sequence)
FROM policy_transaction_fee
WHERE policy_base = @policy_base
) + 1)
,'INS'
,[Installment_Fee]
,8000
,[Entered_Date]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

FETCH NEXT FROM c1
INTO @policy_base

END

CLOSE c1
DEALLOCATE c1

rollback

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-27 : 14:45:37

quote:
Originally posted by chedderslam

SELECT [Policy_Base]
,[Policy_Suffix]
,(( SELECT MAX(policy_transaction_fee.sequence)
FROM policy_transaction_fee
WHERE policy_base = @policy_base
) + 1)
,'INS'
,[Installment_Fee]
,8000
,[Entered_Date]
FROM @policy_transaction_temp
WHERE policy_base = @policy_base

It looks like the select max is causing the issue. How can I get the highest sequence for the current policy_base?

chedderslam
Posting Yak Master

223 Posts

Posted - 2009-04-27 : 15:03:22

Here's the trigger if that helps:

ALTER TRIGGER [dbo].[tiu_policy_transaction_fee]
ON [dbo].[policy_transaction_fee]
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

UPDATE dbo.policy_transaction
SET Policy_Fee = A.Policy_Fee,
SR22_Fee = A.SR22_Fee,
MVR_Fee = A.MVR_Fee,
Installment_Fee = A.Installment_Fee,
NSF_Fee = A.NSF_Fee,
Theft_Prevention_Fee = A.Theft_prevention_Fee,
Umf_Fee = A.UMF_Fee
FROM policy_transaction PT WITH (NOLOCK),
( SELECT PTF.Policy_Base, PTF.Policy_suffix, PTF.Sequence,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(RTRIM(PTF.fee_code),'POL','=',PTF.fee_amount,0))) AS Policy_Fee,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(CHARINDEX(RTRIM(PTF.fee_code),'SR22,FR44'),0,'>',PTF.fee_amount,0))) AS SR22_Fee,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(RTRIM(PTF.fee_code),'MVR','=',PTF.fee_amount,0))) AS MVR_Fee ,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(CHARINDEX(RTRIM(PTF.fee_code),'INF,INS,EINF,EINS'),0,'>',PTF.fee_amount,0))) AS Installment_Fee,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(RTRIM(PTF.fee_code),'NSF','=',PTF.fee_amount,0))) AS NSF_Fee ,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(RTRIM(PTF.fee_code),'TPF','=',PTF.fee_amount,0))) AS Theft_Prevention_Fee ,
SUM(CONVERT(SMALLMONEY,dbo.fn_if(CHARINDEX(RTRIM(PTF.fee_code),'UMF,FLH'),0,'>',PTF.fee_amount,0))) AS Umf_Fee
FROM ( SELECT Policy_Base,
Policy_Suffix,
Sequence
FROM Inserted
GROUP
BY Policy_Base, Policy_suffix, Sequence) AS I
JOIN Policy_Transaction_Fee PTF
ON ( PTF.Policy_Base = I.Policy_Base
AND PTF.Policy_Suffix = I.Policy_Suffix
AND PTF.Sequence = I.Sequence)
GROUP
BY PTF.Policy_Base, PTF.Policy_suffix, PTF.Sequence) AS A
WHERE PT.Policy_Base = A.Policy_Base
AND PT.Policy_Suffix = A.Policy_Suffix
AND PT.Sequence = A.Sequence
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources