Click here to Skip to main content
14,970,162 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Good morning everybody.
I made a sp that works perfectly, but when I create the trigger to execute it it gives error.
I tried to create a trigger with the same sql query but it also doesn't work help me please

[edit]
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). "
[/edit]

What I have tried:

ALTER PROCEDURE [dbo].[sp_SubMensal_update]
AS
BEGIN
update a set
a.SubDiario=s.SubDiario
from EmpAttendance a
join EmpSalary s on s.EmpId=a.EmpId
where a.time_registered > getdate() -1
END

create TRIGGER [dbo].[spSubMensal_update]
   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;

   update a set 
    a.SubDiario=s.SubDiario
from EmpAttendance a 
join EmpSalary s on s.EmpId=a.EmpId -
where a.time_registered > getdate() -1
END
Posted
Updated 14-Apr-21 0:40am
v2
Comments
Richard MacCutchan 14-Apr-21 4:46am
   
Help with what? We have no idea what the error is, where it occurs, what the code is trying to do ...
miguel santos 2021 14-Apr-21 5:48am
   
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). "

Quote:
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). "

Smells like an infinite recursion with trigger.
This means that the trigger execution triggers itself directly or indirectly.
   
Comments
miguel santos 2021 14-Apr-21 6:45am
   
there's any way i can solve it? thanks
Patrice T 14-Apr-21 6:57am
   
Analyze whole set of triggers.
Jörgen Andersson 14-Apr-21 8:48am
   
Your trigger updates the same table that triggers it, that's what's causing the loop.
You need to rethink your approach.
Personally I avoid triggers altogether for everything but logging.
miguel santos 2021 14-Apr-21 9:04am
   
so how can i execute the sp? use localdb which does not have sql agent, the application is in C #
Don't you need a GO statement at the end.
   
Comments
miguel santos 2021 14-Apr-21 6:45am
   
where is the GO statement please?
Tony Hill 14-Apr-21 7:35am
   
It should be placed on its own line after the last line of your code.
miguel santos 2021 14-Apr-21 8:57am
   
brother I am very sorry for my ignorance, but where in the code placed "GO"? can you please give me an example? thank you for your patience

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900