DB Dummy|Sharing Experience on Oracle & Informix Databases …

If you try to change a flag of your record, but you don’t want to make any changes on your application
example :
Your product code use to be “Non Tax”
But now to have to be stored at “With GST”

Below is a sample table structure for illustraction purpose
c1 is the key and c2 is the content :-

create table t1
(
c1 integer,
c2 char(20),
primary key (c1) constraint c1
);

Create the procedure and triggle for the update
The triggle only takes effect for INSERT statement :-

create procedure update_t1_prc(pc1 int) returning CHAR(20);
DEFINE pc2 CHAR(20);

SELECT c2 into pc2
from t1
where c1 = pc1;

IF pc2 is not null THEN
LET pc2 = ‘With GST’;
END IF

return pc2;
end procedure;

create trigger update_t1_trig
insert on t1
REFERENCING NEW AS n
for each row (execute procedure update_t1_prc(n.c1) into c2);

Now, to some insert.
> insert into t1 values (24,’Non Tax’);

1 row(s) inserted.

> select * from t1 where c1=24;

c1 c2

24 With GST

1 row(s) retrieved.

Hope you find this useful