oracle触发器

/*
语句触发器
*/
--before触发器
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN')
in('SAT','SUN') THEN
raise_application_error(-20001,'不能在休息日改变库员号');
end if;
end ;
--使用条件谓词（insert or update or delete）
create or replace trigger t_sec_emp
before insert or update or delete on emp
begin
if to_char(sysdate,'DY','nls_date_language=AMERICAN')
in('SAT','SUN') THEN
case
when inserting then
raise_application_error(-20001,'不能在休息日增加雇员');
when updating then
raise_application_error(-20001,'不能在休息日更改雇员');
when deleting then
raise_application_error(-20001,'不能在休息日删除雇员');
end case;
end if;

end;

--after触发器
create table audit_table(
name varchar2(20),ins int,upd int,del int,starttime date,endtime date
)
/
create or replace trigger tr_audit_emp
after insert or update or delete on emp
declare
v_temp int;
begin
select count(*) into v_temp from audit_table
where name='emp';
if v_temp=0 then
insert into audit_table values('emp',0,0,0,sysdate,null);
end if;
case
when inserting then
update audit_table set ins=ins+1 ,endtime=sysdate where name='emp';
when updating then
update audit_table set upd=upd+1 ,endtime=sysdate where name='emp';
when deleting then
update audit_table set del=del+1 ,endtime=sysdate where name='emp';
end case;
end;


/*
行触发器
*/

--before行触发器
create or replace trigger tr_emp_sal
before update of sal on emp
for each row
begin
IF :new.sal<:old.sal then
raise_application_error(-20010,'工资只上涨不下降');
END IF;
end;

--after

create table audit_emp_change(
name varchar2(32),
oldsal number,
newsal number,
time date
)
/

create or replace trigger tr_sal_change
after update of sal on emp
for each row
declare v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change(name,oldsal,newsal,time) values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal ,newsal=:new.sal ,time=sysdate where name=:old.ename;

end if;
end;

--限制行触发器
create or replace trigger tr_sal_change
after update of sal on emp
for each row
when (old.job='SALESMAN')
declare v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change(name,oldsal,newsal,time) values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal ,newsal=:new.sal ,time=sysdate where name=:old.ename;

end if;

end;

/*
使用DML触发器
*/
--1 控制数据安全
--一般授权
grant select ,insert,update ,delete on emp to SMITH;
--
create or replace trigger tr_emp_time
before update or delete or insert on emp
begin

if to_char(sysdate,'HH24') not between '9' and '17' then
raise_application_error(-20101,'非工作时间');
end if;
end;

--2实现数据审计
--字典审计
audit update,delete,insert on emp by access;

--触发器审计
create or replace trigger tr_sal_change
after update of sal on emp
for each row
declare v_temp int;
begin
select count(*) into v_temp from audit_emp_change where name=:old.ename;
if v_temp=0 then
insert into audit_emp_change(name,oldsal,newsal,time) values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal ,newsal=:new.sal ,time=sysdate where name=:old.ename;

end if;
end;

--3 实现数据完整性
--约束
alter table emp add constraint ch_sal
check (sal>=800)
--
create or replace trigger tr_check_sal
before update of sal on emp
for each row
when (new.sal<old.sal or new.sal>1.2*old.sal)

begin
raise_application_error(-20931,'工资只升不降，并且升幅不能超过20%');
end;
--4 实现参照完整性
--约束
alter table emp add constraint fk_deptno
foreign key (deptno) references dept(deptno)
on delete cascade;
--触发器实现更新完整性
create or replace trigger tr_update_cascade
after update of deptno on dept
for each row
begin
update emp set deptno=:new.deptno where deptno=:old.deptno;
end;
/*
instead-of 触发器
*/
create or replace view dept_emp as
select a.deptno,a.dname,b.empno,b.ename from dept a,emp b
where a.deptno=b.deptno;
/
create or replace trigger tr_instead_of_dept_emp
instead of insert on dept_emp
for each row
declare v_temp int;
begin
select count(*) into v_temp from dept where deptno=:new.deptno;
if v_temp=0 then
insert into dept(deptno,dname) values(:new.deptno,:new.dname);
end if;
select count(*) into v_temp from emp where empno=:new.empno;
if v_temp=0 then
insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
end if;
end;

/*
系统事件触发器
*/

conn sys/sys as sysdba;
create table event_table(event varchar2(30),time date);
/

create or replace trigger tr_startup
after startup on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;
/
/*
登录退出触发器
*/
create table log_table(
name varchar(20),
logon_time date,
logoff_time date,
address varchar2(100)
);
create or replace trigger tr_logon
after logon on database
begin

insert into log_table(name,logon_time,address)
values(ora_login_user,sysdate,ora_client_ip_address);
end;
/

create or replace trigger tr_logon
before logoff on database
begin

insert into log_table(name,logoff_time,address)
values(ora_login_user,sysdate,ora_client_ip_address);
end;

/*
DDL触发器
*/
conn sys/sys as sysdba;

create table event_ddl(
event varchar2(20),
username varchar2(10),
owner varchar2(20),
objname varchar2(20),
objtype varchar2(10),
time date
);

create or replace trigger tr_ddl
after ddl on scott.schema
begin
insert into event_ddl values(ora_sysevent,ora_login_user,ora_dict_obj_owner,ora_dict_obj_name,ora_dict_obj_type,sysdate);
end;

/*
管理触发器
*/
--1显示触发器信息
select trigger_name ,status from user_triggers
where table_name='EMP';
--2禁止触发器
alter trigger tr_sec_emp disable;
--3激活触发器
alter trigger tr_sec_emp enable；
--4禁止或激活所有触发器
alter table emp disable all triggers;
alter table emp enable all triggers;
--5重新编译触发器
--当修改表结构时，触发器状态变为invalid,重新编译可使触发器生效
alter trigger tr_sec_emp compile;