Trigger to count insert and updates from users

Question

I'm new with oracle sql. I am trying to make a trigger that counts when X user performs an update or an insert, but the TRANSACTIONCONTROL table shows it like this:

DATE--------- USER-----------INSERT----UPDATE
10/03/2022  UserParcial       1          0
10/03/2022  UserParcial       0          1
10/03/2022  UserParcial       1          0

But I want it to look like this:

DATE--------- USER-----------INSERT----UPDATE
10/03/2022  UserParcial       2          1

This is my trigger:

create or replace NONEDITIONABLE TRIGGER TRANSACTIONCONTROL_Trig
AFTER INSERT OR DELETE OR UPDATE on products
for each row
DECLARE
dataTran date;
userTran varchar(30); 
InsertTran number:=0;
UpdateTran number:=0;

BEGIN
SELECT SYSDATE INTO dateTran FROM DUAL;
SELECT USER INTO userTran FROM DUAL;

    IF INSERTING THEN
    InsertTran := InsertTran  +1;   
    INSERT INTO TransactionControl(date, user, insert, updates) 
    VALUES(dateTran, userTran, insertTran, updateTran);
    END IF;
    
    IF UPDATING THEN
    updateTran:= updateTran+1;
    INSERT INTO TransactionControl(date, user, insert, updates) 
    VALUES(dateTran, userTran, insertTran, updateTran);
    END IF;

END;

Does this answer your question? [How to get number of rows affected by a statement when inside that statement's trigger](https://stackoverflow.com/questions/8770386/how-to-get-number-of-rows-affected-by-a-statement-when-inside-that-statements-t) — astentx, Mar 11 '22 at 06:14

score 0 · Answer 1 · answered Mar 11 '22 at 07:58

If you don't need exact numbers, than mining ALL_TAB_MODIFICATIONS periodically could probably suffice. (I'm curious as to what business function having the count provides)

But if you really must use a trigger, then a compound trigger lets you keep counts at row level, but then summarise at statement level.

Some pseudo code below

create or replace trigger mytrig
  for insert or update on mytable
    compound trigger

  ins_cnt int;
  upd_cnt int;

  before statement is
  begin
    ins_cnt := 0;
    upd_cnt := 0;
  end before statement;

  after each row is
  begin
    if inserting then ins_cnt := ins_cnt + 1; end if;
    if updating then upd_cnt := upd_cnt + 1; end if;
  end after each row;

  after statement is
  begin
    insert into txn_control ( ... ) values (ins_cnt, upd_cnt);
  end after statement;

end;
/

Trigger to count insert and updates from users

1 Answers1