Oracle数据库---触发器

sql> --当我们对empnew执行删除操作之后，它就会出现一个提示信息，提示：这是删除操作！
sql> create trigger first_trigger
2 after delete
3 on empnew
4 begin
5 dbms_output.put_line('这是删除的操作！');
6 end;
7 /
trigger created

sql> set serveroutput on
sql> delete from empnew where empno = 7788;
这是删除的操作！
1 row deleted

--案例1：禁止scott用户的ddl操作
create or replace trigger scott_trigger
before ddl
on schema
begin
raise_application_error(-20005,'scott用户禁止所有的ddl操作！');
end;

--测试
create sequence test_seq;

--案例2：
--创建一个记录数据库对象ddl操作的日志表
create table object_log(
logid number constraint pk_logid primary key,
operatedate date not null，
objecttype varchar2(50) not null,
objectowner varchar2(50) not null
);

create sequence object_log_seq;

--创建触发实现对数据库对象ddl操作记录的触发器
create or replace trigger object_trigger
after create or drop or alter
on database
begin
insert into object_log(logid,operatedate,objecttype,objectowner)
values(object_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner);
end;

--测试
conn yanln/yanln
create sequence test_seq1;

conn system/password
select * from object_log;

--开发示例1

sql> create or replace trigger emp_trigger1
2 before insert or update or delete
3 on emp
4 begin
5 if to_char(sysdate,'day') in ('星期六','星期日')then
6 raise_application_error(-20006,'不能在休息日改变员工信息！');
7 end if;
8 end;
9 /
trigger created

sql> delete from emp where empno = 7788;
delete from emp where empno = 7788
ora-20006: 不能在休息日改变员工信息！
ora-06512: 在 "scott.emp_trigger1", line 3
ora-04088: 触发器 'scott.emp_trigger1' 执行过程中出错

--开发示例2

--创建审计表
create table delete_emp_audit(
name varchar2(10),
delete_time date
);

--创建触发器
create or replace trigger del_emp_trigger
after delete on emp
for each row
begin
insert into delete_emp_audit values(:old.ename,sysdate);
end;

--测试
delete from emp where empno = 7499;

select * from delete_emp_audit;

--开发示例3
sql> create or replace trigger tr_check_sal
2 before update of sal on emp
3 for each row
4 when (new.sal<old.sal or new.sal>old.sal*1.5)
5 begin
6 raise_application_error(-20028,'工资只升不降，并且升幅不能超过50%');
7 end;
8 /
trigger created

sql> update emp set sal = sal*1.8 where empno = 7788;
update emp set sal = sal*1.8 where empno = 7788
ora-20028: 工资只升不降，并且升幅不能超过50%
ora-06512: 在 "scott.tr_check_sal", line 2
ora-04088: 触发器 'scott.tr_check_sal' 执行过程中出错

--开发示例4
create or replace trigger upd_cascade_trigger
after update of deptno
on dept
for each row
begin
update emp set deptno = :new.deptno where deptno = :old.deptno;
end;

--测试
update dept set deptno = 50 where deptno = 10;

select deptno,ename from emp where deptno = 50;

--创建视图
create or replace view emp_dept
as
select d.deptno,d.dname,e.empno,e.ename
from dept d, emp e
where d.deptno = e.deptno;

--创建替代触发器
create or replace trigger instead_of_trigger
instead of
insert
on emp_dept
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;

--测试
insert into emp_dept values(50,'development',2222,'alice');

select * from emp_dept

--连接sys用户

--创建事件表
create table event_table(
event varchar2(50),
event_time date
);

--再创建一个系统触发器
create or replace trigger startup_trigger
after startup on database
begin
insert into event_table values(ora_sysevent,sysdate);
end;

--在sqlplus窗口执行下列命令
shutdown
startup
select * from event_table;