数据库 触发器二

触发器案例：现有test表，需要针对test表字段C_SEX上的操作，每一次操作都要向一个修改记录表中插入相应的修改的信息。步骤
0 创建表test
1 创建修改记录表 TB_MODIFY_FILED_INFO
2 创建触发器 TRIGGER_TEST_C_SEX_FILED
3 对表进行CRUD操作

0 创建test表

create table TEST
(
  C_ID       VARCHAR2(40) default SYS_GUID() not null,
  C_NAME     VARCHAR2(40),
  C_CID      VARCHAR2(40),
  C_PASSWORD VARCHAR2(40),
  C_SEX      VARCHAR2(40),
  C_ADDRESS  VARCHAR2(255),
  C_TRUENAME VARCHAR2(40),
  C_COMPANY  VARCHAR2(40),
  C_EMAIL    VARCHAR2(40),
  C_FIELD1   VARCHAR2(40),
  C_FIELD2   VARCHAR2(40),
  C_FIELD3   VARCHAR2(40),
  C_CRT_CDE  VARCHAR2(40),
  C_CRT_DATE DATE default sysdate,
  C_UPD_CDE  VARCHAR2(40),
  C_UPD_DATE DATE default sysdate,
  C_DEL      VARCHAR2(1) default '0'
)

1 创建修改记录表 TB_MODIFY_FILED_INFO

create table TB_MODIFY_FILED_INFO
(
  C_ID                    VARCHAR2(40) not null,
  C_TABLE_NAME            VARCHAR2(40) not null,
  C_MODIFY_FIELD          VARCHAR2(40) not null,
  C_CRT_CDE               VARCHAR2(30),
  T_CRT_DATE              DATE,
  C_UPD_CDE               VARCHAR2(30),
  T_UPD_DATE              DATE default SYSDATE,
  C_CONTENT_BEFORE_MODIFY VARCHAR2(40),
  C_CONTENT_AFTER_MODIFY  VARCHAR2(40),
  C_TABLE_ID_MODIFY       VARCHAR2(40)
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table TB_MODIFY_FILED_INFO
  is '修改记录表';
-- Add comments to the columns 
comment on column TB_MODIFY_FILED_INFO.C_ID
  is '主键';
comment on column TB_MODIFY_FILED_INFO.C_TABLE_NAME
  is '修改表名';
comment on column TB_MODIFY_FILED_INFO.C_MODIFY_FIELD
  is '修改字段';
comment on column TB_MODIFY_FILED_INFO.C_CRT_CDE
  is '创建人';
comment on column TB_MODIFY_FILED_INFO.T_CRT_DATE
  is '创建时间';
comment on column TB_MODIFY_FILED_INFO.C_UPD_CDE
  is '更新人';
comment on column TB_MODIFY_FILED_INFO.T_UPD_DATE
  is '更新时间';
comment on column TB_MODIFY_FILED_INFO.C_CONTENT_BEFORE_MODIFY
  is '修改前内容';
comment on column TB_MODIFY_FILED_INFO.C_CONTENT_AFTER_MODIFY
  is '修改后内容';
comment on column TB_MODIFY_FILED_INFO.C_TABLE_ID_MODIFY
  is '修改表主键';
-- Create/Recreate primary, unique and foreign key constraints 
alter table TB_MODIFY_FILED_INFO
  add constraint PK_TB_MODIFY_FILED_INFO primary key (C_ID)

2 关键时刻到了 创建触发器

create or replace trigger TRIGGER_TEST_C_SEX_FILED
  before update of C_SEX on TEST
  FOR EACH ROW
declare
begin
  if (:new.C_SEX != :old.C_SEX) then
    insert into TB_MODIFY_FILED_INFO
      (C_ID,
       C_TABLE_NAME,
       C_TABLE_ID_MODIFY,
       C_MODIFY_FIELD,
       C_CRT_CDE,
       T_CRT_DATE,
       C_UPD_CDE,
       T_UPD_DATE,
       C_CONTENT_BEFORE_MODIFY,
       C_CONTENT_AFTER_MODIFY)
    values
      (sys_guid(),
       'TEST',
       :new.C_ID,
       'C_SEX',
       :new.C_CRT_CDE,
       :new.C_CRT_DATE,
       :new.c_upd_cde,
       :new.C_upd_date,
       :old.C_SEX,
       :new.C_SEX);
  end if;
end;

[b]进行验证[/b]

--对表test的c_sex字段进行操作
update test tt set tt.c_sex = '1' where tt.c_id = '00003'
--查询修改记录表 TB_MODIFY_FILED_INFO 
select t.*, t.rowid from tb_modify_filed_info t

发现确实多了一条记录
更多内容可以baidu，google