Lets say we have this TableA:
EMPNO | EXPCODE | EXPYEARS |
---|---|---|
1 | EXP1 | 4 |
1 | EXP4 | 4 |
The premise of this trigger is that whenever someone makes an update to Table A, EXP4 is not allowed to be less than EXP1.
My first iteration was something along the lines of:
BEFORE INSERT OR UPDATE ON TableA
FOR EACH ROW
DECLARE
EXP NUMBER(7,2);
BEGIN
IF (:NEW.EXPCODE = 'EXP1') THEN
SELECT NVL(X.EXPYEARS,0) INTO EXP FROM TableA X WHERE X.EMPNO = :NEW.EMPNO AND X.EXPCODE = 'EXP4';
IF (EXP < :NEW.EXPYEARS) THEN raise_application_error(-20010,'EXP4 CANNOT BE LESS THAN EXP1'); END IF;
ELSIF (:NEW.EXPCODE = 'EXP4') THEN
SELECT NVL(X.EXPYEARS,0) INTO EXP FROM TableA X WHERE X.EMPNO = :NEW.EMPNO AND X.EXPCODE = 'EXP1';
IF (EXP > :NEW.EXPYEARS) THEN raise_application_error(-20010,'EXP4 CANNOT BE LESS THAN EXP1'); END IF;
END IF;
END;
I've gone through several iterations after that to try and get around this issue but to no avail. Any help would be appreciated. Our company doesn't own the application using the database so I'm trying to make QoL changes on the database side.
CodePudding user response:
You'd need to create a compound trigger (or separate row- and statement-level triggers).
create type empno_t as table of integer;
create or replace trigger check_exp
for insert or update on tableA
compound trigger
l_empnos empno_t := new empno_t();
after each row
is
begin
l_empnos.extend;
l_empnos( l_empnos.count ) := :new.empno;
end after each row;
after statement
is
l_invalid_empnos integer;
begin
select count(*)
into l_invalid_empnos
from tableA expcode1
where expcode1.expcode = 'EXP1'
and expcode1.empno MEMBER OF l_empnos
and exists( select 1
from tableA expcode4
where expcode4.expcode = 'EXP4'
and expcode4.empno = expcode1.empno
and expcode4.expyears < expcode1.expyears );
if( l_invalid_empnos > 0 )
then
raise_application_error( -20001, 'EXP4 expyears > EXP1 expyears ');
end if;
end after statement;
end;
Here is a fiddle that shows the compound trigger working.