oracle中触发器

1. oracle触发器

------------------------------
create table t_booktype(
  id number not null,
  typename varchar2(20),
  num number,
  constraint booktype_pk_id primary key(id)
);

insert into t_booktype(id, typename, num) values (1, '计算机类', 3);
insert into t_booktype(id, typename, num) values (2, '生物类', 1);

create table t_book(
  id number not null,
  bookname varchar2(20),
  typeid number,
  constraint book_pk_id primary key(id),
  constraint book_booktype_fk_id foreign key(typeid) references t_booktype(id)
);

insert into t_book(id, bookname, typeid) values (1, 'java编程思想', 1);
insert into t_book(id, bookname, typeid) values (2, '一头扎进Java', 1);
insert into t_book(id, bookname, typeid) values (3, '生物起源', 2);
insert into t_book(id, bookname, typeid) values (4, 'xx3', 1);

create table t_book_log(
  actionuser varchar2(20),
  actionname varchar2(20),
  actiontime date
);
------------------------------
1) 触发器简介
具备某些条件，由数据库自动执行的一些DML操作行为。

2) 语句触发器
语句触发器针对整个表，作用整个表操作；
语法结构：
create trigger 触发器名称
before/after 触发动作
on作用对象
SQL> create trigger tr_book
before insert
on t_book
begin
  if user!='cc' then
    raise_application_error(-20001,'权限不足');
  end if;
end;
测试：
insert into t_book values(6,'xx7',2);

SQL> create trigger tr_book2
before update or delete
on t_book
begin
  if user!='CC' then
    raise_application_error(-20001,'权限不足');
  end if;
end;
测试：
delete from t_book where id=6;

触发器谓词：inserting、updating、deleting
SQL> create trigger tr_book_log
after insert or update or delete
on t_book
begin
  if updating then
    insert into t_book_log values(user,'update',sysdate);
  else if inserting then
    insert into t_book_log values(user,'insert',sysdate);
  else if deleting then
    insert into t_book_log values(user,'delete',sysdate);
  end if;
  end if;
  end if;
end;

3) 行触发器
行触发器针对行记录。
语法结构：
create trigger 触发器名称
before/after 触发动作
for each row
on 作用对象

触发器内置变量 :old :new
SQL> create trigger tr_book_add
after insert
on t_book
for each row
begin
  update t_booktype set num=num+1 where id=:new.typeId;
end;
SQL> create trigger tr_book_delete
after delete
on t_book
for each row
begin
  update t_booktype set num=num-1 where id=:old.typeId;
end;

4) 触发器禁用和开启
禁用触发器： alter trigger 触发器名称 disable;
SQL> alter trigger tr_book disable;

启用触发器： alter trigger 触发器名称 enable;
SQL> alter trigger tr_book enable;