Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Counting Records in a Transaction Trigger

437599Feb 26 2007 — edited Feb 27 2007
I 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 27 2007
Added on Feb 26 2007
6 comments
1,072 views