oracle触发器

语句触发器针对整个表操作，主要用于权限判断

on作用对象一般是表，视图比较少用

create trigger tr_book
before insert
on t_book
begin
  if user!='cc' then
    raise_application_error(-20001,'权限不足');
  end if;
end;


create trigger tr_book2
before update or delete
on t_book
begin
  if user!='CC' then
    raise_application_error(-20001,'权限不足');
  end if;
end;

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;

行触发器

for each row 

触发器内置变量

:old 记录删除的行记录

:new 获取新增的行记录

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;

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;