0

The SP makes an update on the EmpAttendance table column SubDiario when the change in the table EmpSalary column SubDiario and it works, but when I try to trigger the sp "EXEC [dbo]. [sp_SubMensal_update] gives an error

Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

the SP

    USE [MiniPayroll]
GO
/****** Object:  StoredProcedure [dbo].[SubMensal_update]    Script Date: 14-04-2021 15:46:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[SubMensal_update]


    
AS
BEGIN
    update dbo.EmpAttendance set 
   EmpAttendance.SubDiario= EmpSalary.SubDiario
from dbo.EmpSalary 
join EmpAttendance   on EmpSalary.EmpId=EmpAttendance.EmpId 
where EmpAttendance.time_registered > getdate() -1


END

The Trigger

    USE [MiniPayroll]
GO
/****** Object:  Trigger [dbo].[update_SubMensal]    Script Date: 14-04-2021 16:32:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[update_SubMensal]
   ON  [dbo].[EmpAttendance]
AFTER INSERT,UPDATE,DELETE AS  BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

   EXEC [dbo].[SubMensal_update]
END

thanks

Miguel Santos
  • 1
  • 3
  • Check https://stackoverflow.com/a/33441117/1260204, try adding `go` at the end of the trigger definition code. – Igor Apr 14 '21 at 15:41
  • 2
    You have a trigger that fires when the table `[dbo].[EmpAttendance]` changes, and the trigger itself changes that ***same*** table. Which then causes the the trigger to fire again, which then changes the table again, which then causes the the trigger to fire again... You wrote an infinite loop. Re-write the `UPDATE` statement in your stored procedure to ***ONLY*** update rows that ***NEED*** updating. – MatBailie Apr 14 '21 at 15:45
  • @MatBailie I already tried to put the trigger in [dbo]. [EmpSalary] because only when EmpSalary.SubDiario is changed and I want the new records in EmpAttendance.SubDiario to change the value as EmpSalary.SubDiario is changed SP does it when I run it but when create the trigger gives error – Miguel Santos Apr 14 '21 at 15:56
  • @Igor already tried, gives the same error – Miguel Santos Apr 14 '21 at 15:57
  • 2
    I already explained it to you. When the trigger fires in runs the SP, and it works fine. But because the SP changes the table, the trigger fires again. Thus you have an infinite loop. – MatBailie Apr 14 '21 at 16:01
  • @MatBailie and how to solve it? can you teach or help me please? thanks – Miguel Santos Apr 14 '21 at 16:09
  • You haven't given enough information for us to re-write your logic... Why are you updating the whole table, even if only one row changes? What do you actually WANT/NEED to happen? Why do you need that logic in the SP rather than in the Trigger? Include example data so that we can reproduce your situation. Then explain under what changes you want to make to which rows under which scenarios. THEN we can help you write logic the doesn't go in to an infinite loop. Right now, it just looks like a bad design *because* of the infinite loop. But without more info we can't redesign it. – MatBailie Apr 14 '21 at 16:18
  • Why are you not just running the update in the trigger? Why is there a join to EmpSalary? If you need to only update rows that exist in EmpSalary you should use `exists` – Stu Apr 14 '21 at 16:26
  • @MatBailie the EmpSalary table has the columns: EmpId (FK) JoinDate Salary Inss Irt SubDiary ParcFixa and is where you register an employee. The EmpAttendance table has the columns: EmpId Year Month TotalDays WorkingDays PresentDays AbsentDays LopDays Forward Sub-Holidays Outbn SubNatal Holidays SubMonthly SubDiary time_registered and is where absences, working days etc. are recorded the SubDiario column I saw the value for EmpSalary.SubDiario because it can be changed, the SubMensal column is computed column that gives the monthly value according to the absences etc. – Miguel Santos Apr 14 '21 at 16:36
  • @Stu hoW can i do it? i'm a begginer dont knoW hoW to – Miguel Santos Apr 14 '21 at 16:37
  • Have you read the [UPDATE() trigger function](https://learn.microsoft.com/en-us/sql/t-sql/functions/update-trigger-functions-transact-sql) documentation? So as to avoid infinite recursion your trigger should probably ensure that `update(SubDiario)` is false before executing the stored procedure. – AlwaysLearning Apr 14 '21 at 23:55
  • Thank you all so much for your help. I managed to do what I wanted through the application in C #. Keep up the great to help those like me, who are just starting – Miguel Santos Apr 16 '21 at 06:43

0 Answers0