create table testlog(lnum number, col1 number, col2 number, col3 number)
/
insert into testlog values (1234,1,1,1)
/
insert into testlog values (1233,2,8,1)
/
insert into testlog values (5233,2,8,4)
/

create table testlog2(lnum number,type varchar2(20),pval number,nval number)

CREATE OR REPLACE PROCEDURE pt_log (
inlnum IN number
, intype IN varchar2
, inpval IN number
, innval IN number
)
IS

--

BEGIN



--
IF inpval != innval
THEN
INSERT INTO testlog2
(lnum,type,pval,nval
)
VALUES (inlnum , intype, inpval, innval
);
END IF;

EXCEPTION
WHEN OTHERS
THEN


RAISE;
END pt_log;
/



CREATE OR REPLACE TRIGGER trig_testlog BEFORE UPDATE ON testlog
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
If :new.ctr is null then
:new.ctr:=1;
else


if :old.col1 :new.col1 or :old.col2 :new.col2 or :old.col3 :new.col3 then
:new.ctr:=:old.ctr+1;
end if;


end if;


IF UPDATING ('COL1')
THEN
pt_log (:OLD.lnum
, 'COL1'
, :OLD.COL1
, :NEW.COL1
);
END IF;



IF UPDATING ('COL2')
THEN
pt_log (:OLD.lnum
, 'COL2'
, :OLD.COL2
, :NEW.COL2
);
END IF;


IF UPDATING ('COL3')
THEN
pt_log (:OLD.lnum
, 'COL3'
, :OLD.COL3
, :NEW.COL3
);
END IF;


End;
/

merge into testlog  src
using (SELECT distinct COUNT(*)
OVER(PARTITION BY lnum ) NUM_RECS
FROM testlog2  
) dat
on (src.lnum = dat.lnum )
when matched then update set ctr = NUM_RECS

merge into testlog  src
using (SELECT distinct COUNT(*)
OVER(PARTITION BY lnum ) NUM_RECS
FROM testlog2  
) dat
on (src.lnum = dat.lnum )
when matched then update set ctr = NUM_RECS

src.lnum = dat.lnum

SELECT distinct COUNT(*)
OVER(PARTITION BY lnum ) NUM_RECS
FROM testlog2