I want to write a on delete trigger which on deletion of the particular entries should insert those values to be deleted first into another table and then delete the record. Is it possible. Please let me know @ the earliest.
Sorry to bother u again. This trigger works fine when both table a and b have the same structure. I have a small change in the table b which is the timestamp field in addition to the existing fields of table a to capture the time @ which the record was deleted. Then I have a problem using the code. Can u please help me out.
Remeber, all the trigger is doing is using the old data fields as insert values into a target table. Create the target table any way you want, and modify the insert to record whatever you want. For example:
alter table dept_del add (deleted_dt date);
create or replace trigger dept_del_trig
before delete on dept
for each row
insert into dept_del (deptno,dname,loc, deleted_dt)
values (:old.deptno, :old.dname, :old.loc, sysdate);
delete dept where deptno = 10;
select * from dept;
-- Alter your session so that the date format shows full date + time
alter session set nls_date_format = 'RRRR/DD/YY HH24:MI:SS';