Sybase Table Audit Trigger on Insert and update

Question

I created a table audit trigger in oracle which I'm having a rough time converting it use for Sybase. I have two tables (a base table and a audit table) and what I'm looking for is a trigger which functions as such.

• If a new record is inserted into the base table, the full new record is copied from the base table into the audit table along with the date of insert, the user that entered the record and a note that says 'New Record'.
• It a record was updated in the base table, the full record from the base table is inserted into the audit table except for the updated column which has the new value (plus a note that says 'Updated Record')

Here is an example of what I created in Oracle:

CREATE TABLE baseTable
(
col1 int,
col2 varchar2(10),
col3 varchar2(50),
chg_date DATE,
chg_usr varchar(10),
PRIMARY KEY(col1)
);

CREATE TABLE auditTable
(
aud_id int,
col1 int,
col2 varchar2(10),
col3 varchar2(50),
chg_typ varchar(100)
chg_date DATE,
chg_usr varchar(30),
PRIMARY KEY (aud_id)
);

CREATE OR REPLACE TRIGGER auditBaseTable
AFTER INSERT OR UPDATE ON baseTable
FOR EACH ROW
BEGIN

IF INSERTING THEN
INSERT INTO auditTable (aud_id, col1, col2, col3, chg_typ, chg_date, chg_usr)
VALUES(seq.NEXTVAL, :NEW.col1, :NEW.col2, :NEW.col3, "New Record", SYSDATE, :NEW.chg_usr);

ELSEIF UPDATING ('col2') THEN --col1 is PK hence cannot be updated
INSERT INTO auditTable (aud_id, col1, col2, col3, chg_typ, chg_date, chg_usr)
VALUES(seq.NEXTVAL, :OLD.col1, :NEW.col2, :OLD.col3, "Col2 Updated", SYSDATE, :NEW.chg_usr);
...
...

ENDIF;
END;

I already understand that Sybase is not able to do triggers using 'AFTER'.

Any help you can provide will be greatly appreciated even if you can set me on the right track. Thank you.

Answer 1

You can create trigger for insert, update, delete No need to worry about before or after

Syntax: create trigger trigger_name on table_name for update, insert, delete <>