SQL - Trigger to send email when update is performed

0

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!

    
asked by anonymous 23.11.2018 / 10:05

0 answers