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.