Good morning, I need help on the following:
I have a trigger to send email when an update is performed, but I have a problem!
Whenever the table has update it is an entire grid that loads and sends emails to all rows that contain the closed field = 1
I wanted to compare to the table when creating the cursor but I am not succeeding!
Follow the trigger immediately!
ALTER TRIGGER [dbo].[TRI_fp_email]
ON [dbo].[fp]
for UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM inserted WHERE fechado = 1)
declare @no2 as numeric(5,0), @email2 as varchar(100), @stamp as varchar(25)
DECLARE C_FP CURSOR
for SELECT fpStamp FROM inserted WHERE fechado = 1
open C_FP
FETCH NEXT FROM C_FP INTO @stamp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @email2 = (SELECT top 1 us.email FROM fp join us on fp.no = us.clbno WHERE fpstamp = @stamp group by us.email)
SET @no2 = (SELECT top 1 fp.no FROM fp join us on fp.no = us.clbno WHERE fpstamp = @stamp group by fp.no)
EXEC dbo.emailVaiFerias @no2, @email2, @stamp
FETCH NEXT FROM C_FP INTO @stamp
END
CLOSE C_FP
DEALLOCATE C_FP
end
I have tried it this way but without success!
for SELECT i.fpStamp FROM inserted i JOIN u_fp_temp f on f.fpstamp = i.fpstamp WHERE i.fried = 1 and f closed = 0
Thanks in advance for the help!
Edited!
I just discovered that the problem is that the trigger is executed so the comparison the table that I have to perform does not apply because it has already been edited!
Is there a way to do it before the update?
Edited!
Resolved, I created a new field in the table I made in the program 2 updates the same one with what I want to update in the table and another to update the field of differentiation of the table in order to achieve the desired!
If you have better suggestions, thank them for sharing!