Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
    
Okay sorry, it works now. I dont know why it wasnt but code above is right. If anyone is having problem, maybe syntax FOLLOWS <triggername> can help. But i wonder how to handle errors anyway, thanks. –  Muflix Jun 28 at 19:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.