Trigger only when the value change

Jan 26, 2016 at 3:53 PM
Hi,

Is it possible to have a option to only trigger if the old value in the column change to new value?

example if I execute the below query for 2 times it triggers me 2 triggers which I don't want, I want the trigger to happen only if there is a change in Column Name value not just executing the update statement.

UPDATE Employee SET Name = 'Zak' WHERE EmployeeID = 100;
UPDATE Employee SET Name = 'Zak' WHERE EmployeeID = 100;

Please let me know your thoughts.

Regards,
Zak
Jan 26, 2016 at 6:08 PM
Hi ZahedAli

I think your idea is good.

I'm going to implement this feature in the next days. May be on Monday the new release will be ready.
I hope you can wait some days.

Bye
Jan 26, 2016 at 9:00 PM
Edited Jan 26, 2016 at 9:27 PM
Hi,

Thanks for your quick response, really appreciate.

Yes of course I can wait, however meanwhile I have got a workaround...

You need to update the trigger like this

IF (UPDATE([Column1]) OR UPDATE([Column2]) OR UPDATE([Column3]) OR UPDATE([Column4]))
BEGIN
SET @dmlType = 'Update'

-- Logic to compare old and new value

INSERT INTO @modifiedRecordsTable 
SELECT [m].[Column1], [m].[Column2], [m].[Column3], [m].[Column4] 
FROM MainTable AS [m]
    INNER JOIN INSERTED AS [m_New] ON [m].Column1 = [m_New].Column1
    INNER JOIN DELETED AS [m_Old]   ON [m].Column1 = [m_Old].Column1
WHERE
    [m_New].[Column1] != [m_Old].[Column1]
    OR
    [m_New].[Column2] != [m_Old].[Column2]
    OR
    [m_New].[Column3] != [m_Old].[Column3]
END
ELSE
BEGIN
RETURN
END

only problem here is the Column1 should be fixed always means PrimaryKey. Anyways would wait for your update :)

Thanks & Regards,
Zak
Jan 27, 2016 at 5:10 PM
Forgot to mention we need to consider NULL and datetime2 as well in where clause


For varchar

[m_New].[Column1] != [m_Old].[Column1]
OR
([m_New].[Column1] IS NOT NULL AND [m_Old].[Column1] IS NULL) -- Handling Null for [Column1]
OR
([m_New].[Column1] IS NULL AND [m_Old].[Column1] IS NOT NULL) -- Handling Null for [Column1]

for datetime2

((DATEDIFF(day,[m_New].[Column2],[m_Old].[Column2])) != 0)
OR
([m_New].[Column2] IS NOT NULL AND [m_Old].[Column2] IS NULL) -- Handling Null for [Column2]
OR
([m_New].[Column2] IS NULL AND [m_Old].[Column2] IS NOT NULL) -- Handling Null for [Column2]
Jan 28, 2016 at 7:33 PM
Edited Jan 28, 2016 at 7:35 PM
update

found you don't need to add the master table and join. Since only one row at a time is provided for insert or update., below is the updated one

IF (UPDATE([Column1]) OR UPDATE([Column2]) OR UPDATE([Column3]) OR UPDATE([Column4]))
BEGIN
SET @dmlType = 'Update'

-- Logic to compare old and new value

INSERT INTO @modifiedRecordsTable
SELECT
 [m_New].[Column1], 
 [m_New].[Column2], 
 [m_New].[Column3], 
 [m_New].[Column4] 
FROM
INSERTED AS [m_New], DELETED AS [m_Old]
WHERE
[m_New].[Column1] != [m_Old].[Column1] 
OR 
([m_New].[Column1] IS NOT NULL AND [m_Old].[Column1] IS NULL) -- Handling Null for [Column1] 
OR 
([m_New].[Column1] IS NULL AND [m_Old].[Column1] IS NOT NULL) -- Handling Null for [Column1] 
OR 
[m_New].[Column2] != [m_Old].[Column2] 
OR 
([m_New].[Column2] IS NOT NULL AND [m_Old].[Column2] IS NULL) -- Handling Null for [Column2] 
OR 
([m_New].[Column2] IS NULL AND [m_Old].[Column2] IS NOT NULL) -- Handling Null for [Column2] 
OR 
[m_New].[Column3] != [m_Old].[Column3] 
OR 
([m_New].[Column3] IS NOT NULL AND [m_Old].[Column3] IS NULL) -- Handling Null for [Column3] 
OR 
([m_New].[Column3] IS NULL AND [m_Old].[Column3] IS NOT NULL) -- Handling Null for [Column3] 
OR 
[m_New].[Column4] != [m_Old].[Column4] 
OR 
([m_New].[Column4] IS NOT NULL AND [m_Old].[Column4] IS NULL) -- Handling Null for [Column4] 
OR 
([m_New].[Column4] IS NULL AND [m_Old].[Column4] IS NOT NULL) -- Handling Null for [Column4] 
END
ELSE
BEGIN
RETURN
END


Regards,
Zak
Jan 28, 2016 at 8:59 PM
i Thanks for your suggestion. However is not enough because we have to keep in consideration XML columns type, where a simple = conditio cannot e applyed.

Then I have to consider table containing timestap column: in this case we cannot apply the above logic because, even if all fields are the same during the update, the timestamp is modified.

I am working on it.
Jan 29, 2016 at 9:28 AM
Ciao Zak

Could you please contact me bv skype.
my skype contact is: christian.delbianco

ths
Jan 29, 2016 at 10:14 AM
Ciao Zak

Could you please contact me by skype because I need your help.
Jan 29, 2016 at 12:04 PM
DONE !

What for SqlTableDependency ver. 4.5.2.0

Bye
Marked as answer by ZahedAli on 1/29/2016 at 8:10 AM
Jan 29, 2016 at 4:09 PM
Hi Delbianco,

Tested and it's working fine, i liked the idea of BINARY_CHECKSUM.

Thanks!!!

Regards,
Zak
Jan 29, 2016 at 7:40 PM
If for you in not a problem,
can you RATE with some STARS and put a comment ?
Thanks.
Jan 29, 2016 at 11:23 PM
Sure!!! Your tool deserves more ratings really happy.

Thanks & Regards,
Zak
May 7, 2016 at 4:06 AM
Dear admin,
About issue: Trigger only when the value change
I would like to know that issue has been fixed in OracleTableDependency yet?

Answer help me quickly please!

thanks so much.