比如说我有一个表A:
列1       列2   
username  age

当我在A里插入数据之后,跟踪表B
列1       列2    列3         列4          列5        列6
username  age   createtime  updatetime  deletetime  sate

记录了相同数据,并且使createtime字段的值为当前系统的时间,字段SATE的值为I.

更新删除的操作跟上无异,只是SATE的值分别为U和D
这里要注意,A表中被删除的数据,在B表中不被删除.

在线请求各为大虾相助,不胜感激~~~~

create or replace trigger inupdetable
  before update or delete or insert on table_a
  for each row
declare
  c number;
begin
--判断数据库内是否有数据
  select count(*) into c from table_b where id = :new.id;

--没有数据的操作
  if c = 0 then
       --插入
    if inserting then
      insert into table_b(id,username,age,createtime,sate)
      values
        (:new.id,
         :new.username,
         :new.age,
         sysdate,
         'I');
        
      --更新
    elsif updating then
      insert into table_b(id,username,age,updatetime,sate)
      values
        (:new.id,
         :new.username,
         :new.age,
         sysdate,
         'U');
        
       --删除
    elsif deleting then
      update table_b
         set username        = :old.username,
             age             = :old.age,
             deletetime      = sysdate,
             sate             = 'D'            
       where id              = :old.id;
    end if;
    
  --有数据的操作
  else
        --插入
    if inserting then
      update table_b
         set username        = :new.username,
             age             = :new.age,
             createtime      = sysdate,
             sate            = 'I'            
       where id              = :new.id;
       
       --删除
    elsif deleting then
      update table_b
         set username        = :old.username,
             age             = :old.age,
             deletetime      = sysdate,
             sate            = 'D'            
       where id               = :new.id;
       
       --更新
    elsif updating then
      update table_b
         set username        = :new.username,
             age              = :new.age,
             updatetime      = sysdate,
             sate            = 'U'
       where id              = :new.id;
       
    end if;
  end if;
end;