Trigger for Audit Table in Sybase ASE 15.7

Question

I'm trying to create a trigger that records info on insertions, updates, and deletions. To make things simple I'm simply trying to create a trigger for insertions without including info such as user and date.

My referenced table is with columns:

DefaultProperties

-DefaultPropertiesID

-Key_

-Value

-ProcessID_FK (foreign key)

============================

and my audit table is:

DefaultProperties_Audit

-OldDefaultID

-OldKey_

-OldValue

-OldProcessID_FK

The code for my current trigger is:

create trigger DPTrigger on DefaultProperties
   for insert as
   begin
       insert into DefaultProperties_Audit(OldDefaultID, OldKey_, OldValue, OldPid_FK, Modifier, EntryDate, Operation)
       select DefaultPropertiesID, Key_, Value, ProcessID_FK, user_name(), GETDATE(), 'insertion' from inserted
   end

but how do I do something like this?

create trigger DPTrigger on DefaultProperties
    for insert, update, delete as
    if insert then
        begin
            insert into DefaultProperties_Audit(OldDefaultID, OldKey_, OldValue, OldPid_FK, Modifier, EntryDate, Operation)
            select DefaultPropertiesID, Key_, Value, ProcessID_FK, user_name(), GETDATE(), 'insertion' from inserted
        end
    endif
    if update then
        doSomething()
    endif
    if delete then
        doSomethingElse()
    endif

When I try to compile my version of the above, it throws me an error. I understand an alternative is to create three separate triggers for insert, update, and delete. I was just wondering if I can condense it into one?

Also, it seems like I can condense it into one if I explicitly state if update(col1) or update(col2) or update(col3) but that seems to also possibly bring up some errors.

Any tips/hints to make this work and to improve it would be greatly appreciated.

Answer 1

ASE triggers support the update() function; there is no such thing as a insert() function.

While the update() function can be used for insert triggers, update() is typically used in update triggers to determine which columns have been updated.

In this particular case, since you plan on inserting the whole row into your audit table, and obviously (?) all of the columns are new, you'll likely find something like the following to be more useful:

create trigger dbo.DPTrigger on dbo.DefaultProperties
    for insert as
    begin
        insert into DefaultProperties_Audit(OldDefaultID, OldKey_, OldValue, OldPid_FK)
        select DefaultPropertiesID, Key_, ProcessID_FK, Value from  inserted
    end

For create trigger syntax, examples, and some discussion of trigger coding see ASE - Reference Manual: Commands - create trigger

For more discussion on trigger usage as it pertains to RI and transactional control see ASE - TSQL Users Guide - Triggers: Enforcing RI constraints

---

As for the updated question asking about consolidating the insert/update/delete code into a single trigger ...

The easiest way to determine if the trigger fired on an insert, update or delete is to test for the existence of rows in the pseudo-tables named inserted and deleted; you can either test for the existence every time the trigger needs to know the type of parent DML, or test once and store in a @variable:

create trigger ...
begin
    declare @operation char(1) -- 'I'nsert, 'U'pdate, 'D'elete
    if exists(select 1 from inserted)
        select @operation = 'I'
    if exists(select 1 from deleted)
        select @operation = case when @operation = 'I' then 'U' else 'D' end

    ... reset of trigger code ...
end