update within a tran - SQL Server Forums

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)

update within a tran

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 203
ERROR: process attempted to update reported policy_transaction
Msg 3609, Level 16, State 1, Line 6
The 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 ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tu_policy_transaction]
ON [dbo].[policy_transaction] FOR UPDATE
AS
DECLARE @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 */

BEGIN
SELECT @numrows = @@ROWCOUNT
IF @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_cursor
FETCH 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 effectively
relate 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_type
END
CLOSE tu_policy_transaction_cursor
DEALLOCATE 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_cursor2
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_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_date
END

CLOSE tu_policy_transaction_cursor2

DEALLOCATE tu_policy_transaction_cursor2
END

--new part added for print process

IF (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.sequence
END

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 rollback
BEGIN
if update(finance_balance) ---- This is the second conidtional that seals your fate.
begin
RAISERROR ('ERROR: process attempted to update reported policy_transaction', 16, 1)
ROLLBACK TRAN

An infinite universe is the ultimate cartesian product.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources