Compound triggers can count and display the number of rows modified without changing the original code.
There are still a few issues and challenges here. This solution probably won't work with parallel DML - it either won't count properly or the triggers will prevent direct-path writes. It probably will work in a multi-user environment but that needs to be tested. You'll also need to build code for DELETEs, and maybe MERGEs. And this will probably slow down DML.
Sample Schema
create table users(id number, username varchar2(100));
insert into users values(1, 'system');
create table table1(col1 number, updated_at date, updated_by number);
insert into table1 values(1, null, null);
insert into table1 values(2, null, null);
create table table2(col1 number, updated_at date, updated_by number);
Package to prevent excessive DBMS_OUTPUT
Constantly printing output can cause problems. So we want to disable output by default. And you probably don't simply want to use DBMS_OUTPUT.DISABLE, that might turn off something else and it's hard to always remember to run that.
Create a simple package with a global variable.
create or replace package print_feedback is
--Outputing large amounts of data can sometimes break things.
--Only enable DBMS_OUTPUT when explicitly requested.
g_print_output boolean := false;
end;
/
Set it to TRUE before running import.
--Run this block first to enable printing.
begin
print_feedback.g_print_output := true;
end;
/
PL/SQL Block to create INSERT and UPDATE triggers
This code dynamically generates triggers to capture INSERTs and UPDATEs.
Dynamic PL/SQL is a bit tricky. Notice I used templates and the alternative quoting mechanism to avoid concatenation hell. Once you understand those tricks the code becomes relatively readable. (And hopefully your IDE understands how the q'[ works better than the StackOverflow syntax highlighter.)
--Create automatic UPDATE and INSERT feedback triggers.
declare
c_sql_template constant varchar2(32767) :=
q'[
create or replace trigger #TABLE_NAME#_#UPD_or_INS#_trg for #UPDATE_OR_INSERT# on #TABLE_NAME# compound trigger
--Purpose: Print a feedback message after updates and inserts.
g_count number := 0;
after each row is
begin
g_count := g_count + 1;
end after each row;
after statement is
begin
if print_feedback.g_print_output then
if g_count = 1 then
dbms_output.put_line('#Inserted_or_Updated# '||g_count||' row in #TABLE_NAME#');
else
dbms_output.put_line('#Inserted_or_Updated# '||g_count||' rows in #TABLE_NAME#');
end if;
end if;
end after statement;
end;
]';
v_sql varchar2(32767);
begin
--Loop through the relevant tables
for tables in
(
select table_name
from user_tables
where table_name in ('TABLE1', 'TABLE2')
order by table_name
) loop
--Create and execute update trigger.
v_sql := replace(replace(replace(replace(c_sql_template
, '#TABLE_NAME#', tables.table_name)
, '#UPD_or_INS#', 'upd')
, '#UPDATE_OR_INSERT#', 'update')
, '#Inserted_or_Updated#', 'Updated');
execute immediate v_sql;
--Create and execute insert trigger.
v_sql := replace(replace(replace(replace(c_sql_template
, '#TABLE_NAME#', tables.table_name)
, '#UPD_or_INS#', 'ins')
, '#UPDATE_OR_INSERT#', 'insert')
, '#Inserted_or_Updated#', 'Inserted');
execute immediate v_sql;
end loop;
end;
/
Sample run
Now your unchanged script will display some output. (I did make a few trivial changes to the script but only to make it runnable.)
SQL> --Run this block first to enable printing.
SQL> set serveroutput on;
SQL> begin
2 print_feedback.g_print_output := true;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> DECLARE
2 v_user_id users.id%TYPE;
3 BEGIN
4 SELECT id INTO v_user_id FROM users WHERE username = 'system';
5
6 UPDATE table1
7 SET col1 = 1,--value,
8 updated_at = SYSDATE,
9 updated_by = v_user_id
10 WHERE 1=1;--some condition;
11
12 INSERT INTO table2 values(2/*val1*/, SYSDATE, v_user_id);
13 END;
14 /
Updated 2 rows in TABLE1
Inserted 1 row in TABLE2
PL/SQL procedure successfully completed.
SQL>
