CODEWITHSUNDEEP
oracle-databasetriggers
Freeman
Freeman

Reputation: 51

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(rownum,'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?

Upvotes: 1

Views: 4627

Answers (2)

user38123
user38123

Reputation: 669

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.

Upvotes: 3

Erich Kitzmueller
Erich Kitzmueller

Reputation: 36987

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.

Upvotes: 1

Related Questions