Hi Tom,

we are trying to create table/column value(count) constraint by using trigger.
what i am trying is, for table A, column name:'value'. this column should not allow more than two 'YES' values. if it encounter any third 'YES' value it has to roll back the transaction and should not commit. i tried with below code. but the Rollback is not working as excepted. please can you advice.

create or replace TRIGGER "ETL_TEST"
BEFORE INSERT OR UPDATE ON TMP_USER
FOR EACH ROW
DECLARE
Record_Count NUMBER;
In_Value VARCHAR2(8) := 'YES';
PRAGMA AUTONOMOUS_TRANSACTION;
ex_invalid_id EXCEPTION;
BEGIN
EXECUTE IMMEDIATE 'select count(VALUE) from TMP_USER where VALUE = ''' || In_Value || '''' INTO Record_Count;
dbms_output.put_line('RECORD_COUNT : ' || Record_Count) ;
if (Record_Count >= 2)
then
ROLLBACK;
dbms_output.put_line('MESSAGE: THE VALUE CANT INSERT MORE THAN 2 TIMES') ;
end;
ELSE
COMMIT;
end if;
END;