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?
– Andrey
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
– Bruno Alves
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!
– Bruno Alves
Instead of retrieving all records from the inserted table where closed = 1 why not check whether only the changed record closed = 1?
– anonimo
But how can I do it?
– Bruno Alves