create domain dmnQUATER as integer check (value in (1, 2, 3, 4));
create domain dmnINTYEAR as integer check (value > 1998);
create table MY_TABLE
( DOC_NO integer not null,
DOC_QUATER dmnQUATER not null,
DOC_YEAR dmnINTYEAR not null,
);
create generator DOC_NO_GEN;
set term !! ;
create trigger SAMPLE_BEFORE_INSERT_TRIGGER for MY_TABLE
before insert position 0 as
declare variable NewDocNo integer;
begin
new.DOC_NO = gen_id(DOC_NO_GEN, 1);
select max (DOC_NO) + 1
from MY_TABLE a
where (a.DOC_QUATER = new.DOC_QUATER)
and (a.DOC_YEAR = new.DOC_YEAR)
into :NewDocNo;
if (NewDocNo is null) then
new.DOC_NO = 1;
else
new.DOC_NO = NewDocNo;
end !!
|