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