SQL - Trigger to send email when updated

Asked

Viewed 308 times

0

Good morning, I need help next time:

I have a Trigger to email when an update is performed, but I have a problem!

Whenever the table has update is an entire grid that is loaded and sends emails to all lines containing the closed field = 1

I wanted to compare with the table when creating the cursor but am not succeeding!

Follow the Rigger in a row!

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’ve 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.closed = 1 and f.closed = 0

Thanks in advance for your help!

Edited!

Eventually I discovered that the problem is that Trigger runs so the comparison to the table I have to perform does not apply because it is already edited!

There is way to perform before the update?

Edited !

Solved, I created new field in the table I performed in the program 2 updates the same one with what I intend to update in the table and another to update the differentiation field of the table in order to achieve the desired!

If you have better suggestions, please share!

  • I don’t quite understand what you want. Can you be clearer or more objective?

  • Basically, this Rigger is executed whenever a table in a vfp application is executed and updates to the table that has Trigger! And this sending duplicate emails because I can’t compare inserted with the table itself

  • The VFP Grid is for holiday approval and with Trigger I intend to send an email when it is approved, but since vfp updates all lines, it always sends emails even the ones you already sent. I am trying to load the cursor with only the ones that have difference but without success. I hope I have been clearer with what I intend!

  • Instead of retrieving all records from the inserted table where closed = 1 why not check whether only the changed record closed = 1?

  • But how can I do it?

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.