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.
Author |
Topic |
chedderslam
Posting Yak Master
223 Posts |
Posted - 2009-04-22 : 17:26:49
|
I am testing an update within a "begin tran" and "rollback" block. I get this error:Msg 50000, Level 16, State 1, Procedure tu_policy_transaction, Line 203ERROR: process attempted to update reported policy_transactionMsg 3609, Level 16, State 1, Line 6The transaction ended in the trigger. The batch has been aborted.Can I not do this? |
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-04-22 : 17:38:31
|
What is the trigger doing?An infinite universe is the ultimate cartesian product. |
|
|
chedderslam
Posting Yak Master
223 Posts |
Posted - 2009-04-22 : 17:49:31
|
Here's the trigger, it's kind of long. Sorry for posting so much. I don't know much about triggers and this is one of the tables my group rarely touches.USE [sufi]GO/****** Object: Trigger [dbo].[tu_policy_transaction] Script Date: 04/22/2009 16:48:25 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[tu_policy_transaction]ON [dbo].[policy_transaction] FOR UPDATEASDECLARE @numrows int, @numnull int, @errno int, @errmsg varchar(255), @policy_base varchar(12), @policy_suffix smallint, @sequence int, @Endorsement smallint, @billing_method char(1), @producer int, @finance int, @Posted_date datetime, @check_date datetime, @override_flag char(1), @Calling_Application varchar(35), @print_line varchar(255), @check_today datetime, @sm_policy_fee smallmoney, @sm_total_fees smallmoney, @sm_paid smallmoney, @sm_pure_premium smallmoney, @sm_posted datetime, @s_earned_flag char(1), @sm_perdayrate smallmoney, @sm_net_amount smallmoney, @dt_equity_date datetime, @i_rate_table smallint, @dt_inception datetime, @dt_expiration datetime, @i_equity_days int, @company char(1), @subcompany char(1), @tran_accounting_period varchar(6), @accounting_period varchar(6), @override_status char(1), @transaction_type char(2), @period_type char(1), @effective_date datetime, @new_accounting_period varchar(6), @charge_in_flag char(1) /*Modified KMD 2/6/2003 Added functionality to re-populate the accounting period if NP or RN transaction is being updated to a future effective date. *//*Modified KMD 6/1/2003 Added check for HRM to not change transactions posted previous day*//*Modified KMD 12/17/2003 – Added @posted_date is null*/ /*Modified KMD 6/1/2004 - Added functionality to check for updating columns in prior accounting period*//* CMC 04-07-07 CQ 5776: Allow changes to transactions in previous accounting periods if the user is a member of the 'charge_in_role' role and the apptable.charge_in_flag has been set to 'Y'.*//* GDL 10/26/2007 CQ5776: Noticed that the apptable queries did not include company / subcompany. It didn't result in error, but theoretically it should have since there is more than one row in the table*//* CMC 11-27-07 CQ5776: changing the logic to an OR statement because the way it's written, they'll cancel each other out */BEGINSELECT @numrows = @@ROWCOUNTIF @numrows = 0 RETURN /* GDL CQ5776 The apptable has more than one row for the conditions stated below, we should use company/subcompany to cut the result down to one row. The current version works in sql server, but it's not right!SELECT @override_flag = Override_Flag FROM Apptable WHERE Application_Name = 'policy'IF (@override_flag IS NULL) SELECT @override_flag = 'N'*/ SELECT @check_date = GETDATE()SELECT @check_today = (CONVERT(char(2),DATEPART(MM,@check_date))) +'/'+ (convert(char(2),(datepart(DD,@check_date)))) +'/'+ DATENAME(YY,@check_date)SELECT @check_date = RTRIM(CONVERT(char(2),DATEPART(MM,@check_date))) + '/1/' + DATENAME(YY,@check_date)DECLARE tu_policy_transaction_cursor CURSOR FOR SELECT Policy_Base, Policy_Suffix, Sequence, Posted_Date, Company, Subcompany, Accounting_Period, transaction_type FROM deleted OPEN tu_policy_transaction_cursorFETCH NEXT FROM tu_policy_transaction_cursor INTO @Policy_Base, @Policy_Suffix, @Sequence, @Posted_Date, @company, @subcompany, @tran_accounting_period, @transaction_type WHILE (@@FETCH_STATUS = 0)BEGIN /*Get the company/subcompany from the policy_base because the update trigger is fired from the insert trigger and company/subcompany may be null since the insert trigger populates these fields KMD 4/16/02*/ Select @company = substring(@policy_base, 1, 1) Select @subcompany = substring(@policy_base, 2, 1)/* ------------------------------------------------------------- CQ5776 GDL Moved the checks for override flag and charge in flag here because we need to query with company subcompany to ensure there is only one row and furthermore if there is more than one transaction not all transactions are guaranteed to have the same company subcompany -------------------------------------------------------------*/ SELECT @override_flag = Override_Flag FROM Apptable WHERE Application_Name = 'policy' and company = @company and subcompany = @subcompany IF (@override_flag IS NULL) SELECT @override_flag = 'N' /* CMC 04-07-07 Call 5776: Allow changes to transactions in previous accounting periods if the user is a member of the 'charge_in_role' role and the apptable.charge_in_flag has been set to 'Y'.*/ SELECT @charge_in_flag = charge_in_flag FROM Apptable WHERE Application_Name = 'policy' and company = @company and subcompany = @subcompany /* CMC 11-27-07 Call 5776: changing the following logic to an OR statement because the way it's written, they'll cancel each other out *//* IF @override_flag = 'Y' and is_member('override') = 1 begin select @override_status = 'Y' end Else begin select @override_status = 'N' end IF @charge_in_flag = 'Y' and is_member('charge_in_role') = 1 begin select @override_status = 'Y' end Else begin select @override_status = 'N' end*/ IF ((@override_flag = 'Y' and is_member('override') = 1) OR (@charge_in_flag = 'Y' and is_member('charge_in_role') = 1 )) begin select @override_status = 'Y' end Else begin select @override_status = 'N' end/* ------------------------------------------------------------- CQ5776 GDL End changes -------------------------------------------------------------*/ IF(@transaction_type = 'PY' or @transaction_type = 'CL' or @transaction_type = 'AD' or @transaction_type = 'NS') select @period_type = 'A' else select @period_type = 'U' Select @accounting_period = accounting_period from company_accounting_periods where company = @company and subcompany = @subcompany and current_period = 'Y' and period_type = @period_type If @accounting_period is null begin RAISERROR ('ERROR: No current billing period found', 16, 1) ROLLBACK TRAN Return end if @tran_accounting_period is null or @tran_accounting_period = ' ' begin select @tran_accounting_period = @accounting_period end SELECT @Calling_Application = APP_NAME() /*Modified KMD 6/1/2004 - Added functionality to check for updating columns in prior accounting period*/ IF (@override_status = 'N' and ((@tran_accounting_period < @accounting_period))) begin -- RAISERROR (@override_flag, 16, 1) RAISERROR ('ERROR: process attempted to update reported policy_transaction', 16, 1) ROLLBACK TRAN CLOSE tu_policy_transaction_cursor DEALLOCATE tu_policy_transaction_cursor RETURN end if (@company = '2' and NOT @Posted_Date IS NULL AND @Posted_Date < @check_today) BEGIN if update(finance_balance) begin RAISERROR ('ERROR: process attempted to update reported policy_transaction', 16, 1) ROLLBACK TRAN CLOSE tu_policy_transaction_cursor DEALLOCATE tu_policy_transaction_cursor RETURN end END /*If these fields are always properly set by the application and any other update process,this section of code can be removed. Note the endorsement number must be set to effectivelyrelate the transaction to the policy condition at the time the transaction is posted.*/ IF (@posted_date IS NULL) BEGIN SELECT @endorsement = NULL, @billing_method = NULL, @producer = NULL, @finance = NULL SELECT @endorsement = Endorsement, @billing_method = Billing_method, @producer = Producer, @finance = Finance FROM Policy (NOLOCK) WHERE Policy_Base = @policy_base AND Policy_Suffix = @policy_suffix AND Current_Flag = 'Y' IF (@@ROWCOUNT = 0) BEGIN SELECT @endorsement = Endorsement, @billing_method = Billing_method, @producer = Producer, @finance = Finance FROM Policy (NOLOCK) WHERE Policy_Base = @policy_base AND Policy_Suffix = @policy_suffix AND Endorsement = 0 END /* Modification: 09-05-2001 LZ - Update Billing Method*/ IF NOT @billing_method IS NULL BEGIN UPDATE Policy_Transaction SET billing_method = @billing_method WHERE Policy_base = @policy_base AND Policy_suffix = @policy_suffix AND Sequence = @Sequence END END FETCH NEXT FROM tu_policy_transaction_cursor INTO @Policy_Base, @Policy_Suffix, @Sequence, @Posted_Date, @company, @subcompany, @tran_accounting_period, @transaction_typeENDCLOSE tu_policy_transaction_cursorDEALLOCATE tu_policy_transaction_cursor DECLARE tu_policy_transaction_cursor2 CURSOR FOR SELECT Policy_Base, Policy_Suffix, Sequence, Posted_Date, Company, Subcompany, Accounting_Period, transaction_type, billing_method, effective_date FROM inserted OPEN tu_policy_transaction_cursor2FETCH NEXT FROM tu_policy_transaction_cursor2 INTO @Policy_Base, @Policy_Suffix, @Sequence, @Posted_Date, @company, @subcompany, @tran_accounting_period, @transaction_type, @billing_method, @effective_date WHILE (@@FETCH_STATUS = 0)BEGIN /*Get the company/subcompany from the policy_base because the update trigger is fired from the insert trigger and company/subcompany may be null since the insert trigger populates these fields KMD 4/16/02*/ Select @company = substring(@policy_base, 1, 1) Select @subcompany = substring(@policy_base, 2, 1) IF(@transaction_type = 'PY' or @transaction_type = 'CL' or @transaction_type = 'AD' or @transaction_type = 'NS') select @period_type = 'A' else select @period_type = 'U' Select @accounting_period = accounting_period from company_accounting_periods where company = @company and subcompany = @subcompany and current_period = 'Y' and period_type = @period_type If @accounting_period is null begin RAISERROR ('ERROR: No current billing period found', 16, 1) ROLLBACK TRAN Return end if @tran_accounting_period is null begin select @tran_accounting_period = @accounting_period end SELECT @Calling_Application = APP_NAME() /*Modified KMD 6/1/2004 - Added functionality to check for updating columns in prior accounting period*/ IF (@override_status = 'N' and ((@tran_accounting_period < @accounting_period))) begin RAISERROR ('ERROR: process attempted to update reported policy_transaction', 16, 1) ROLLBACK TRAN CLOSE tu_policy_transaction_cursor2 DEALLOCATE tu_policy_transaction_cursor2 RETURN end if (@company = '2' and NOT @Posted_Date IS NULL AND @Posted_Date < @check_today) BEGIN if update(finance_balance) begin RAISERROR ('ERROR: process attempted to update reported policy_transaction', 16, 1) ROLLBACK TRAN CLOSE tu_policy_transaction_cursor2 DEALLOCATE tu_policy_transaction_cursor2 RETURN end END /*Modified KMD 2/6/2003 Begin */ /*Modified KMD 12/17/2003 – Added @posted_date is null*/ if ((@transaction_type = 'NP') or (@transaction_type = 'RN')) and @billing_method <> 'C' and @posted_date is null and @override_status = 'N' begin Select @new_accounting_period = accounting_period from company_accounting_periods where company = @company and subcompany = @subcompany and period_begin <= @effective_date and period_end >=@effective_date and period_type = @period_type If (@accounting_period is null or @new_accounting_period is null ) begin RAISERROR ('ERROR: No current billing period found', 16, 1) ROLLBACK TRAN Return end If @new_accounting_period > @accounting_period begin select @accounting_period = @new_accounting_period end UPDATE Policy_Transaction SET accounting_period = @accounting_period WHERE Policy_base = @policy_base and Policy_suffix = @policy_suffix and Sequence = @Sequence end/*Modified KMD 2/6/2003 END*/ if NOT @Posted_Date IS NULL begin /* Determine the equity date and set it for policy KMD 12/12/00*/ select @sm_policy_fee = sum(policy_fee), @sm_total_fees = (sum(policy_fee) + sum(sr22_fee) + sum(mvr_fee) + sum(theft_prevention_fee) + sum(nsf_fee) + sum(installment_fee) + sum(umf_fee)), @sm_paid = sum(premium_paid), @sm_pure_premium = sum(pure_premium) from policy_transaction (nolock) where policy_base = @policy_base and policy_suffix = @policy_suffix and posted_date is not null select @i_rate_table = rate_table, @dt_inception = inception_date, @dt_expiration = expiration_date from policy (nolock) where policy_base = @policy_base and policy_suffix = @policy_suffix and current_flag = 'Y' select @s_earned_flag = policy_fee_earned_flag from rate_table (nolock) where rate_table = @i_rate_table if @sm_paid <= 0 begin select @dt_equity_date = @dt_inception end else if @s_earned_flag = 'Y' begin select @sm_perdayrate = round((@sm_pure_premium + @sm_policy_fee)/datediff(day, @dt_inception, @dt_expiration), 2) select @sm_net_amount = @sm_paid - @sm_total_fees + @sm_policy_fee end else begin select @sm_perdayrate = round(@sm_pure_premium/datediff(day, @dt_inception, @dt_expiration), 2) select @sm_net_amount = @sm_paid - @sm_total_fees end if @sm_net_amount > 0 and @sm_perdayrate > 0 begin select @i_equity_days = round(@sm_net_amount/@sm_perdayrate, 0) select @dt_equity_date = dateadd(day, @i_equity_days, @dt_inception) if @dt_equity_date > @dt_expiration begin select @dt_equity_date = @dt_expiration end end else begin select @dt_equity_date = @dt_inception end update policy_information set equity_date = @dt_equity_date where policy_base = @policy_base and policy_suffix = @policy_suffix end FETCH NEXT FROM tu_policy_transaction_cursor2 INTO @Policy_Base, @Policy_Suffix, @Sequence, @Posted_Date, @company, @subcompany, @tran_accounting_period, @transaction_type, @billing_method, @effective_dateENDCLOSE tu_policy_transaction_cursor2DEALLOCATE tu_policy_transaction_cursor2END--new part added for print processIF (suser_sname() <> 'print_mgr' AND suser_sname() <> 'print_np')BEGIN UPDATE policy_transaction SET policy_transaction.updated_by = ( SELECT user_id FROM users WHERE login = suser_sname() ), policy_transaction.updated_date = getdate() FROM inserted, policy_transaction WHERE inserted.policy_base = policy_transaction.policy_base and inserted.policy_suffix = policy_transaction.policy_suffix and inserted.sequence = policy_transaction.sequenceEND |
|
|
cat_jesus
Aged Yak Warrior
547 Posts |
Posted - 2009-04-22 : 18:01:39
|
It looks like the trigger is doing the rollback and throwing the error.see line 203 of the trigger.if (@company = '2' and NOT @Posted_Date IS NULL AND @Posted_Date < @check_today) ------This is the first conditional that starts your rollbackBEGINif update(finance_balance) ---- This is the second conidtional that seals your fate.beginRAISERROR ('ERROR: process attempted to update reported policy_transaction', 16, 1)ROLLBACK TRAN An infinite universe is the ultimate cartesian product. |
|
|
|
|
|
|
|