How to write a trigger 1

[Gerilee]

I need to write a trigger/triggers to create an audit trail of the activity of an existing INFORMIX database. This is INFORMIX-SE 7.2. I've been reading the documentation but I'm getting more confused the more I read. Could someone give me an example to get me started? I have a table named control and I want to write to another table called hold_control (which is a duplicate of the control table) in the same database everytime there are any changes (updates, inserts, or deletes) on the control table. Also, I'd like to add a column to the hold_control table to indicate what type of change was made to the control table (update, insert, or delete). Is this possible? Thanks.

 

[vpshriyan]

Hi,

Here are few sample codes for implimentation of triggers. Base table is tab1 and trace table is tab2. Insert,update and delete triggers call a common procedure tab2_proc. Hope you will find this piece of code useful and relevant.

create trigger tab2_ins insert on tab1
referencing old as pre_upd
for each row
(
execute procedure tab2_proc(f1, f2, f3,'I')
);

create trigger tab2_upd update on tab1
referencing old as pre_upd
for each row
(
execute procedure tab2_proc(f1,f2,f3,'U')
);

create trigger tab2_del delete on tab1
referencing old as pre_del
for each row
(
execute procedure tab2_proc(f1,f2,f3,'D')
);

create procedure tab2_proc(
mf1 char(8) ,
mf2 char(7) ,
mf3 decimal(15,2),
mode char(1))

define cnt int;
define mrem varchar(50);

set lock mode to wait;
if mode = 'I' then
let mrem='INSERTED ON ' || today || ' BY ' || user;
end if
if mode = 'U' then
let mrem='UPDATED ON ' || today || ' BY ' || user;
end if
if mode = 'D' then
let mrem='DELETED ON ' || today || ' BY ' || user;
end if

while 1=1
insert into tab2 (mf1, mf2, mf3, mrem);

select dbinfo('sqlca.sqlerrd2') into cnt from systables where tabid=1;
if cnt is null then
let cnt=0;
end if
if cnt >=1 then
exit while;
end if
end while

end procedure;

You may download an e-book (pdf) version of Trigger from:

http://publibfi.boulder.ibm.com/epubs/pdf/7230a.pdf

Regards,
Shriyan

 

[vpshriyan]

Gerilee:

Oops! There is a slight mistake. Sorry!

In case of INSERT trigger the is no state as OLD, so the corrected trigger will look like:

create trigger tab2_ins insert on tab1
referencing new as post_ins
for each row
(
execute procedure tab2_proc(f1, f2, f3,'I')
);

Regards,
Shriyan