Prevent UPDATE TRIGGER to fire when no changes made in SYBASE

Question

In Sybase I have a main table test and an audit table test_a. The audit table is updated by triggers on insert, update and delete. Now the issue is, when I use update query with old data (no changes), the trigger still gets fired and logs in audit table, which I don't want (to prevent duplication when no changes).

test has

Id | NAME | DESC

test_a has

updated_by | date | Id | NAME | DESC

This is the update trigger I have:

create trigger test_utrig on test for update as  
insert into audit..test_a select 'update',update_by(),getdate() inserted.* from inserted

I tried to use if (update(Id) or update(NAME) or update(DESC)), but still the update trigger get fired on no changes. Please help me how to stop trigger from firing when no changes are done.

Answer 1

I don't know SyBase but from the documentation, it appears as if you can check whether one or more columns are updated. It should be sufficient to check if one is updated like:

create trigger test_utrig on test 
for update as  
    if update (id)
        insert into audit..test_a (updated_by, date, Id, ,NAME, DESC)
        select update_by(), getdate(), inserted.Id, inserted.NAME, inserted.DESC 
        from inserted

It appears to be a mismatch between the number of columns in the trigger and in the audit table, so I removed the constant 'update'.

Answer 2

You should check not only if you have information in the inserted table, but also check if is different from the deleted one.

Test has

Id | NAME | DESC

Test_a has

Event | updated_by | date | Id | NAME | DESC

This trigger prevents the Id to be changed and checks if there is any changes in the other columns. If there is any modification inserts it in the Test_a table.

CREATE TRIGGER test_utrig on test for update as 
    IF UPDATE(Id)
    BEGIN
        raiserror 9000 "Can't change Id"
        RETURN
    END 
    INSERT INTO audit..test_a
    SELECT 'Update',
            update_by(),
            getdate(),
            i.* 
    FROM
        inserted i,
        deleted  d
    WHERE
        i.Id = d.Id
    AND (   i.NAME <> d.NAME
         OR i.DESC <> d.DESC)

Hope it helps.

Answer 3

Your sample trigger appears to be T-SQL so I'm assuming you're using ASE (as opposed to IQ, SQLAnywhere or Advantage), so fwiw ...

You've stated ... "update query with old data (no changes)" ... which is contradictory in ASE since any update does perform a change of data (yes, even if the old and new values are the same).

Suppose you run the following:

update tableA
set    col1 = 5
where  col1 = 5

Logically we might say the column has not been updated/changed.

But technically the column has been updated, ie, Sybase ASE will perform an update of the column. This in turn means the if update(col1)... will always return true if col1 shows up as the target of a 'set' clause.

Sybase ASE does not compare the old and new values and then, if the same value is being referenced, 'ignore' the update. And no, this is not a case of a piece of stupid/dumb software but rather the software doing what you tell it to do.

I've seen on a few occasions where this kind of update is used to force some sort of downstream activity; one simple example would be the forcing of an update of a 'last updated' flag in a display program (think of a stock/forex ticker that periodically updates the 'last updated' date/time stamp even though the price may not have changed, thus signalling to the user that the display hasn't frozen/hung).

I've also seen on more than a few occasions where the (T-SQL) programmer wasn't paying attention and therefore wasted some cpu cycles with an unnecessary update. [It gets really noticeable if the updated column is part of an index as the index entry must be deleted and then (re)inserted ... thus causing a (slow/poorly-performing) deferred update.]

Short of not performing the update, you would need to code your trigger to compare the old/deleted and new/inserted values of the column and then take what you deem is the appropriate action.

Answer 4

Got the solution:

CREATE TRIGGER test_utrig
ON test
FOR UPDATE
AS
if UPDATE(id) and exists(select id from inserted where id not in (select id from inserted))
begin
insert into test_a select 
  'update',
  UPDATE_BY(),
  GETDATE(),
  inserted.*
FROM
  inserted