How to use SQL trigger to record the affected column's row number

I want to have an 'updateinfo' table in order to record every update/insert/delete operations on another table.

In oracle I've written this:

CREATE TABLE updateinfo ( rnumber NUMBER(10), tablename VARCHAR2(100 BYTE), action VARCHAR2(100 BYTE), UPDATE_DATE date )

DROP TRIGGER TRI_TABLE;

CREATE OR REPLACE TRIGGER TRI_TABLE

AFTER DELETE OR INSERT OR UPDATE

ON demo

REFERENCING NEW AS NEW OLD AS OLD

FOR EACH ROW

BEGIN

if inserting then

insert into updateinfo(rnumber,tablename,action,update_date ) values(rownum,'demo', 'insert',sysdate);

elsif updating then

insert into updateinfo(rnumber,tablename,action,update_date ) values(row开发者_运维技巧num,'demo', 'update',sysdate);

elsif deleting then

insert into updateinfo(rnumber,tablename,action,update_date ) values(rownum,'demo', 'delete',sysdate);

end if;

-- EXCEPTION

-- WHEN OTHERS THEN

-- Consider logging the error and then re-raise

-- RAISE;

END TRI_TABLE;

but when checking updateinfo, all rnumber column is zero. is there anyway to retrieve the correct row number?

---

The only option is to use primary key column of your "demo" table.

ROWNUM is not what you are looking for, read the explanation.

ROWID looks like a solution, but in fact it isn't, because it shouldn't be stored for a later use.

---

ROWNUM is not what you think it is. ROWNUM is a counter that has only a meaning within the context of one execution of a statement (i.e. the first resulting row always has rownum=1 etc.). I guess you are looking for ROWID, which identifies a row.