I created table in new schema/user
CREATE TABLE archive.table1 ( id NUMBER(10), name VARCHAR(45), created_at DATE, updated_at DATE, deleted_at DATE );
I granted access to that table for production schema user.
grant insert on archive.table1 to db_production;
I created trigger in production schema which will move the rows to the archive on delete.
CREATE OR REPLACE TRIGGER table1_archiving_on_delete_trg BEFORE DELETE ON table1 FOR EACH ROW BEGIN insert into archive.table1 (id, name, created_at, updated_at, deleted_at) values (:old.id, :old.name, :old.created_at, :old.updated_at, sysdate); END;
But if i delete row
delete from table1 where id=1;
nothing in my archive
select * from archive.table1;
Because i have no idea why it is not working im thinking about some error handling in trigger, because i think there is mistake and its good idea too. But i dont know how to write an exception in trigger.
EXCEPTION insert into trigger_errors (id, trigger_name, error_description, date) values (1,'table1_archiving_on_delete_trg, '##SomeErrorVariable/Code?## ,sysdate)
So i have 2 questions :
1: Why it does not work, where is mistake and how i can solve it ?
2: How i can handle errors in trigger ? I mean generated errors not raise my own.
Thanks for your helps !
Here is solution for error handling :
EXCEPTION WHEN OTHERS THEN err_code := SQLCODE; err_msg := SUBSTR(SQLERRM, 1, 200); insert into db_production.errors (type, name, error_number, error_message) values ('trigger','table1_archiving_on_delete_trg', err_code, err_msg); END;