How to use SQL trigger to record the affected column's row number
Posted
by Freeman
on Stack Overflow
See other posts from Stack Overflow
or by Freeman
Published on 2010-04-14T06:43:56Z
Indexed on
2010/04/14
12:03 UTC
Read the original article
Hit count: 207
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?
© Stack Overflow or respective owner