How to use SQL trigger to record the affected column's row number
- by Freeman
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?
