I have a condition where I need to update a column ( it is basically a flag which has to be set to “Y”) for a row and at the same time I need to take care that the number of rows having flag value = Y should not be more than 5.
Oracle Version 220.127.116.11.0
U can see there are 3 rows having the flag values is equal to "Y" , now if i try to update the flag for a_var =2 , 3 more values will get updated but my requirement is if at any point of time the number of rows having flag = "Y" is more then 5 then an error message should be thrown to the use.
Create table test1 ( a_var number, a_flag varchar2(1) ); insert into test1 values (1, 'Y'); insert into test1 values (1, 'Y'); insert into test1 values (2, 'N'); insert into test1 values (2, 'N'); insert into test1 values (2, 'N'); insert into test1 D values (3, 'Y');
results in this:
create or replace trigger test1_max_y for insert or update on test1 COMPOUND TRIGGER type t_test1 is table of test1%rowtype index by pls_integer; g_test1 t_test1; c_index pls_integer := 0; procedure check_y_number (p_new_values in test1%rowtype) IS --This procedure is overloaded so that it can be used for multiple tables v_count number; BEGIN -- SELECT count(*) INTO v_count from test1 WHERE a_flag = 'Y'; -- if v_count > 5 then raise_application_error(-20000,'Statement will result in more than 5 y''s'); end if; -- END; before statement is begin --initialise the collection g_test1.delete; c_index := 0; end before statement; after each row is c_index constant pls_integer := g_test1.count + 1; begin g_test1(c_index).a_var := :new.a_var; g_test1(c_index).a_flag := :new.a_flag; end after each row; after statement is begin for i in 1..g_test1.count loop check_y_number(g_test1(i)); END loop; end after statement; end; / show errors;
be aware of the concurrency issues though.
SQL> select * 2 from test1 3 / A_VAR A ---------- - 1 Y 1 Y 2 N 2 N 2 N 3 Y 6 rows selected. SQL> update test1 set a_flag = 'Y' where a_var = 2; update test1 set a_flag = 'Y' where a_var = 2 * ERROR at line 1: ORA-20000: Statement will result in more than 5 y's ORA-06512: at "WHITEHAT.TEST1_MAX_Y", line 21 ORA-06512: at "WHITEHAT.TEST1_MAX_Y", line 49 ORA-04088: error during execution of trigger 'WHITEHAT.TEST1_MAX_Y'
AbSHeik wrote:Did you try what John suggested?
create materialized view log on test1 with rowid; materialized view LOG created. create materialized view test_mview refresh on commit as select sum(decode(a_flag,'Y',1,0)) y_cnt from test1; materialized view TEST_MVIEW created alter materialized view test_mview add constraint chk_y_vnt check(y_cnt <= 5 ); materialized view TEST_MVIEW altered. select * from test_mview; Y_CNT ----- 3 update test1 set a_flag='Y' where a_flag='N' and rownum=1; 1 rows updated. update test1 set a_flag='Y' where a_flag='N' and rownum=1; 1 rows updated. commit; committed. select * from test_mview; Y_CNT ----- 5 update test1 set a_flag='Y' where a_flag='N' and rownum=1; 1 rows updated. commit; SQL Error: ORA-12008: error in materialized view refresh path ORA-02290: check constraint (HR.CHK_Y_VNT) violated 12008. 00000 - "error in materialized view refresh path"