November 16, 2016 at 10:41 am
Hi All,
I have created a trigger in the table below
CREATE TABLE [dbo].[BillingAccountDetails](
[BillingInfoId] [int] NULL,
[BillingLevelId] [int] NULL,
[BillingTypeId] [int] NULL,
[BillTypeId] [int] NULL,
[AgentAccountNumber] [varchar](10) NULL,
[AgentSubAccountNumber] [int] NULL,
[InsuredAccountNumber] [varchar](10) NULL,
[InsuredSubAccountNumber] [int] NULL,
[AddressInformation] [varchar](2000) NULL
) ON [PRIMARY]
But im getting a strange exception in the application log while user is doing some operation which hits this trigger.
com.microsoft.sqlserver.jdbc.SQLServerException: Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
The strange thing is 95% percentage of time, it is working without issues, only sometimes it throws the above error.
Not sure what is the problem...
Please advise....
CREATE TRIGGER [dbo].[tI_BillingAccountDetails]
ON [dbo].[BillingAccountDetails]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @BillingInfoId int,
@BillingLevelId int,
@BillingTypeId int,
@BillTypeId int,
@AgentAccountNumber varchar(10),
@AgentSubAccountNumber int,
@InsuredAccountNumber varchar(10),
@InsuredSubAccountNumber int,
@AddressInformation varchar(2000),
@ii_BillTypeId int,
@i_AgentAccountNumber varchar(10),
@i_AgentSubAccountNumber int,
@i_InsuredAccountNumber varchar(10),
@i_InsuredSubAccountNumber int,
@i_AddressInformation varchar(2000),
@AccountNumber varchar(10),
@i_BillingInfoId int,
@ii_BillingInfoId int,
@i_BillTypeId int
select @BillingInfoId=inserted.BillingInfoId from inserted
select @BillingLevelId=inserted.BillingLevelId from inserted
select @BillingTypeId=inserted.BillingTypeId from inserted
select @i_BillTypeId=inserted.BillTypeId from inserted
select @i_AgentAccountNumber=inserted.AgentAccountNumber from inserted
select @i_AgentSubAccountNumber=inserted.AgentSubAccountNumber from inserted
select @i_InsuredAccountNumber=inserted.InsuredAccountNumber from inserted
select @i_InsuredSubAccountNumber=inserted.InsuredSubAccountNumber from inserted
select @i_AddressInformation = inserted.AddressInformation from inserted
if @BillingLevelId=1
Begin
select @AccountNumber=AccountNumber from CimAccount where BillingInfoId=@BillingInfoId
if exists(select * from CimAccount where AccountNumber=@AccountNumber and BillingInfoId<>@BillingInfoId)
Begin
if exists (select * from BillingAccountDetails where BillingInfoId in (select BillingInfoId from CimAccount where AccountNumber=@AccountNumber and BillingInfoId<>@BillingInfoId and BillingInfoId<>0) and BillingTypeId=@BillingTypeId and BillingLevelId=1)
Begin
select @i_BillingInfoId=max(BillingInfoId) from BillingAccountDetails where BillingInfoId in (select BillingInfoId from CimAccount where AccountNumber=@AccountNumber and BillingInfoId<>@BillingInfoId and BillingInfoId<>0) and BillingTypeId=@BillingTypeId and BillingLevelId=1
select @BillTypeId=BillTypeId,
@AgentAccountNumber=AgentAccountNumber,
@AgentSubAccountNumber=AgentSubAccountNumber,
@InsuredAccountNumber=InsuredAccountNumber,
@InsuredSubAccountNumber=InsuredSubAccountNumber,
@AddressInformation = AddressInformation
from BillingAccountDetails
where BillingInfoId=@i_BillingInfoId
and BillingTypeId=@BillingTypeId
and BillingLevelId=1
if @BillingTypeId=1
Begin
if @i_BillTypeId<>@BillTypeId
Begin
set @BillTypeId=@i_BillTypeId
End
Update BillingAccountDetails
set BillTypeId=@BillTypeId,
AgentAccountNumber=@AgentAccountNumber,
AgentSubAccountNumber=@AgentSubAccountNumber,
InsuredAccountNumber=@InsuredAccountNumber,
InsuredSubAccountNumber=@InsuredSubAccountNumber,
AddressInformation=@AddressInformation
where BillingInfoId=@BillingInfoId
and BillingTypeId=@BillingTypeId
and BillingLevelId=1
End
else
Begin
if (@BillTypeId<>@i_BillTypeId or @AgentAccountNumber<>@i_AgentAccountNumber or @AgentSubAccountNumber<>@i_AgentSubAccountNumber or @InsuredAccountNumber<>@i_InsuredAccountNumber or @InsuredSubAccountNumber<>@i_InsuredSubAccountNumber or @AddressInformation<>@i_AddressInformation)
Begin
Update BillingAccountDetails
set BillTypeId=@BillTypeId,
AgentAccountNumber=@AgentAccountNumber,
AgentSubAccountNumber=@AgentSubAccountNumber,
InsuredAccountNumber=@InsuredAccountNumber,
InsuredSubAccountNumber=@InsuredSubAccountNumber,
AddressInformation=@AddressInformation
where BillingInfoId=@BillingInfoId
and BillingTypeId=@BillingTypeId
and BillingLevelId=1
End
End
End
Else
Begin
select @AccountNumber=AccountNumber from CimAccount where BillingInfoId=@BillingInfoId
Declare Billing_Account_Details Cursor local For
select distinct BillingInfoId from CimAccount where AccountNumber=@AccountNumber and BillingInfoId<>@BillingInfoId and BillingInfoId<>0
Open Billing_Account_Details
Fetch Next From Billing_Account_Details into @ii_BillingInfoId
While @@FETCH_STATUS = 0
Begin
select @BillTypeId=inserted.BillTypeId from inserted
select @AgentAccountNumber=inserted.AgentAccountNumber from inserted
select @AgentSubAccountNumber=inserted.AgentSubAccountNumber from inserted
select @InsuredAccountNumber=inserted.InsuredAccountNumber from inserted
select @InsuredSubAccountNumber=inserted.InsuredSubAccountNumber from inserted
select @AddressInformation = inserted.AddressInformation from inserted
Insert Into BillingAccountDetails values(@ii_BillingInfoId,@BillingLevelId,@BillingTypeId,@BillTypeId,@AgentAccountNumber,@AgentSubAccountNumber,@InsuredAccountNumber,@InsuredSubAccountNumber,@AddressInformation)
Fetch Next From Billing_Account_Details into @ii_BillingInfoId
End
Deallocate Billing_Account_Details
End
End
End
-- Insert statements for trigger here
END
GO
November 16, 2016 at 12:11 pm
Is it my imagination, or are you firing INSERTs in a loop against the same table the trigger is firing on? You have a cursor inside your trigger that's firing INSERT statements, which will, in turn, fire the trigger. Once you hit the maximum nesting level and then it throws the error. The maximum level is there to protect us from ourselves and it appears that it's done so in this case. This is one of those logic problems that only you can answer.
Another item of note is that the INSERTs look like they could be converted into a single INSERT statement that processes all the rows at once instead of one by one, but that won't do anything to address the logic problem.
November 16, 2016 at 4:08 pm
Ed Wagner (11/16/2016)
Is it my imagination, or are you firing INSERTs in a loop against the same table the trigger is firing on? You have a cursor inside your trigger that's firing INSERT statements, which will, in turn, fire the trigger. Once you hit the maximum nesting level and then it throws the error. The maximum level is there to protect us from ourselves and it appears that it's done so in this case. This is one of those logic problems that only you can answer.Another item of note is that the INSERTs look like they could be converted into a single INSERT statement that processes all the rows at once instead of one by one, but that won't do anything to address the logic problem.
Hi Ed,
Thanks for the explanation ....
Im really confused what am i doing wrong in here... What im trying to do is, once a user tries to add a record into the BillingAccountDetails table for a particular account.
Im trying to get all the billinginfo id for that particular account from cimaccount table and update the same information for other billinginfoids.
Basically its like ,
When user tries to insert one record for account = 0123456 and billinginfoid = 17.
This trigger will find all the billinginfoid for that account 0123456 . Then it will insert new records into the BillingAccountDetails for all the billinginfo id.
This what im trying to achieve as part of this trigger and it is working for most of the accounts.
I can understand that im doing something wrong here but not sure what is the best practice.
Please advise....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply