Skip to Main Content

trigger causing mutation or maximum number of recusive sql error

user7955917Feb 1 2012 — edited Feb 1 2012
ID,*SID*,STATUS,*PID*
1,5000,Validating,
3,5001,processing,5000
4,5002,processing,5000
5,5003,complete,5000
6,5004,complete,5000
7,5005,complete,5000

is it possible to create a trigger to update the STATUS column of row 1 ie "1,5000,*Validating*" to "1,5000,*complete*" when the rows where the id values are from 3 to 7 STATUS value turns to complete by means of update .


CREATE OR REPLACE PACKAGE pkg_blk_trg AS

PROCEDURE blk_forrow(p_id IN blk_upd.id%TYPE);

PROCEDURE blk_stmt_exec;

END pkg_blk_trg;
/


CREATE OR REPLACE PACKAGE BODY pkg_blk_trg AS

TYPE blk_rec IS RECORD (
id blk_upd.id%TYPE
);

TYPE t_blk_nt IS TABLE OF blk_rec;
g_blk_nt t_blk_nt := t_blk_nt();

PROCEDURE blk_forrow (p_id IN blk_upd.id%TYPE ) IS
BEGIN
g_blk_nt.extend;
g_blk_nt(g_blk_nt.last).id := p_id;
END blk_forrow;

PROCEDURE blk_stmt_exec IS
BEGIN
FOR i IN g_blk_nt.first .. g_blk_nt.last
LOOP
UPDATE blk_upd b1
SET b1.status = 'COMPLETE'
WHERE b1.pid IS NULL
AND b1.sid IN (SELECT DISTINCT a.pid
FROM (SELECT b2.pid,
COUNT(*) OVER (PARTITION BY b2.pid) tot_recs,
SUM(CASE b2.status WHEN 'complete' THEN 1 ELSE 0 END) OVER (PARTITION BY b2.pid) complete_recs
FROM blk_upd b2
WHERE b2.pid IS NOT NULL
and b2.pid = g_blk_nt(i).id) a
WHERE a.tot_recs = a.complete_recs);

END LOOP;
g_blk_nt.delete;
END blk_stmt_exec;

END pkg_blk_trg;
/

CREATE OR REPLACE TRIGGER blk_rl_trriger
AFTER INSERT OR UPDATE ON blk_upd
FOR EACH ROW
when (new.pid is not null)
BEGIN
pkg_blk_trg.blk_forrow(p_id => :new.id);
dbms_output.put_line('rowlevel trigger fired');
END;
/


create or replace trigger blk_st_trriger
after insert or update on blk_upd
BEGIN
pkg_blk_trg.blk_stmt_exec;
dbms_output.put_line('statementlevel trigger fired');
END;
Comments
Post Details
Added on Feb 1 2012
3 comments
59 views