Prevent UPDATE TRIGGER to fire when no changes made in SYBASE

sybasetriggerupdate

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.

Best Answer

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'.

Related Question