Counting Records in a Transaction Trigger
437599Feb 26 2007 — edited Feb 27 2007I would like to keep a count of the records that come into our database by date, so want to build an insert trigger on the table (if you don't see date=sysdate in the stats table create a new stats record, otherwise just add one to the counter where date=sysdate).
This is fairly straightforward if I use a BEFORE INSERT ON EACH ROW trigger, however for performance reasons I'd prefer to make this a transaction trigger (probably AFTER INSERT).
Our application brings in records and commits hundreds at a time. Is there a way I can get the number of records commited in the current transaction so I can add that to my counter?
Hope this is clear. Currently I have:
CREATE OR REPLACE
TRIGGER table1_insert
BEFORE insert ON table1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
last_date date;
v_rows_processed integer;
BEGIN
update stats_table set record_count = record_count + 1 where to_char(stats_date,'yyyymmdd') = to_char(sysdate,'yyyymmdd');
v_rows_processed := SQL%ROWCOUNT;
if v_rows_processed = 0 then
insert into stats_table (stats_date,record_count) values(sysdate,1);
end if;
END table1_insert;
/
Is there any way I can do this as a transaction trigger?
Thanks