--触发事件:insert,delete,update
--触发时机:before,after
--触发表:触发器为之工作的表
--触发类型(频度):行级触发器,语句级(表级)触发器
--触发操作: 触发器中的语句块。
--一个触发器可以被多个事件触发,一个触发器可以有多个事件
---行级前触发器
create or replace trigger tri_row_before
before update on emp
for each row
declare
-- local variables here
begin
dbms_output.put_line('---行级前触发器----');
end tri_row_before;
---行级后触发器
create or replace trigger tri_row_after
after update on emp
for each row
declare
-- local variables here
begin
dbms_output.put_line('---行级后触发器----');
end tri_row_after;
--表级前触发
create or replace trigger tri_tab_before
before update on emp
declare
-- local variables here
begin
dbms_output.put_line('---表级前触发器----');
end tri_tab_before;
--表级后触发
create or replace trigger tri_tab_after
after update on emp
declare
-- local variables here
begin
dbms_output.put_line('---表级后触发器----');
end tri_tab_after;
----------------------------------------------------------------------------------------------------------------------------------------------------
执行结果:
SQL> update emp set ename=upper(ename) where deptno=10;
---表级前触发器----
---行级前触发器----
---行级后触发器----
---行级前触发器----
---行级后触发器----
---行级前触发器----
---行级后触发器----
---表级后触发器----
3 rows updated
---------------------------------------------------------------------------------------------------------------------------------------------------
1、先复制两张表使用
SQL> create table HYD.emp as select * from scott.emp;
Table created
SQL> create table HYD.dept as select * from scott.dept;
Table created
2、复制emp表
create table emp2 as select * from emp;
----------------------------------------------------------------------------------------------------------------------------------------------------
--1、如果是周末就不能对员工表进行操作
-- dy指的是星期几
create or replace trigger tri_emp2_dml
before insert or update or delete on emp2
declare
v_day varchar2(20);
begin
select to_char(sysdate,'dy') into v_day from dual;
if v_day='星期六' or v_day='星期日' then
raise_application_error(-20001,'休息日不能对员工表进行操作');
end if;
end tri_emp2_dml;
----------------------------------------------------------------------------------------------------------------------------------------------------
SQL> update emp2 set ename=upper(ename);
update emp2 set ename=upper(ename)
ORA-20001: 休息日不能对员工表进行操作
ORA-06512: 在 "HYD.TRI_EMP2_DML", line 6
ORA-04088: 触发器 'HYD.TRI_EMP2_DML' 执行过程中出错
----------------------------------------------------------------------------------------------------------------------------------------------------
这里的触发器就好像是过滤器,让你看下我能不能做操作
----------------------------------------------------------------------------------------------------------------------------------------------------
--触发器的执行:
--DML操作请求--->触发器工作-->DML操作结束--->commit/rollback;
--触发器中不能含有事务控制语句commit/rollback;
触发器操作是属于DML操作事务的一部分
不能含有DDL语句,因为DDL语句会自动提交
触发器的代码不能超过512k,可以使用触发器调用函数或者过程解决极大的代码定义问题。
----------------------------------------------------------------------------------------------------------------------------------------------------
--条件谓词
create or replace trigger tri_emp2_dml
before insert or update or delete on emp2
declare
v_day varchar2(20);
begin
select to_char(sysdate,'dy') into v_day from dual;
if v_day='星期六' or v_day='星期日' then
if inserting then
raise_application_error(-20001,'休息日不能对员工表进行插入');
elsif updating then
raise_application_error(-20001,'休息日不能对员工表进行修改');
elsif deleting then
raise_application_error(-20001,'休息日不能对员工表进行删除');
end if;
end if;
end tri_emp2_dml;
----------------------------------------------------------------------------------------------------------------------------------------------------
注意:无论是前触发器还是后触发器,都是在DML操作前更新
--after触发器和before触发器的区别
1、触发时机,前触发器在后触发器之前
2、行级前触发器可以修改:new的值,而行级后触发器不行
:new新记录(插入)
:old旧记录(删除)
--在update的时候既可以I用:new,也可以用:old
--注意: :new 或者 :old 只能在行级触发器中使用
--在语句级触发器中使用:new 或者 :old ,会报编译错误层
----------------------------------------------------------------------------------------------------------------------------------------------------
用触发器实现级联删除
级联删除:外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动被删除。
父表——被外键引用的表
子表——引用父表中的键作为外键的表
--先将dept的deptno关联到emp2中做外键,此时对dept进行删除会报错
SQL> delete from dept where deptno=10;
delete from dept where deptno=10
ORA-02292: 违反完整约束条件 (HYD.FK_D_DEPT) - 已找到子记录
----------------------------------------------------------------------------------------------------------------------------------------------------
创建触发器:
create or replace trigger tri_dept
before delete on dept ---把这里的before改成after同样效果可以执行,因为无论是前触发器还是后触发器,都是在DML操作前更新
for each row ---DML操作在内存中会有一个缓存的过程
declare
-- local variables here
begin
delete from emp2 where deptno=:old.deptno;
end tri_dept;
----------------------------------------------------------------------------------------------------------------------------------------------------
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 10.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 ----已经没有部门编号为10的员工了
SQL> delete from dept where deptno=10;
1 row deleted
SQL> select * from dept;
DEPTNO DNAME LOC
------ -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON --已经没有部门为10
SQL> select * from emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 10.00 20
7654 MARTIN SALESMAN 7698 1981/9/28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981/5/1 2850.00 30 ----已经没有部门编号为10的员工了
SQL> select * from emp2;
---------------------------------------------------------------------------------------------------------------------------------------------------
具体表信息:
-- Create table
create table DEPT
(
DEPTNO NUMBER(2) not null,
DNAME VARCHAR2(14),
LOC VARCHAR2(13)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table DEPT
add constraint PK_DEPT primary key (DEPTNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
---------------------------------------------------------------------------------------------------------------------------------------------------
-- Create table
create table EMP2
(
EMPNO NUMBER(4) not null,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table EMP2
add constraint PK_EMP primary key (EMPNO)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
alter table EMP2
add constraint FK_D_DEPT foreign key (DEPTNO)
references DEPT (DEPTNO);
-------------------------------------------------------------------------------------------------------------------------------------
insert into tab values(sq_tab.nextval,'a');
insert into tab values(sq_tab.nextval,'b');
insert into tab values(200,'c'); --这样不按照规则去执行会出问题的,所以我们添加触发器。
create or replace trigger tri_tab_insert
before insert on tab
for each row
declare
v_num number;
begin
select sq_tab.nextval into v_num from dual;
:new.tabid :=v_num;
end tri_tab_insert; ---无论你输入了什么id,我们都默认按照nextval去自增
而且这种方法也可以避免输入同一个主键
但是如果是行级后触发器就会出问题,因为我们需要的是:new在进缓存之前就进行修改
--------------------------------------------------------------------------------------------------------------------------------------------------------------
instead of触发器(视图触发器)
因为这是给视图做文章的
简单视图:来自于一张表,多张表则称为复杂视图
视图一般只用来做查询不做修改
instead of触发器和普通DML触发器的区别是什么?
普通触发器是DML操作事务的一部分
--DML操作请求--->触发器工作-->DML操作结束--->commit/rollback;
instead of触发器会结束当前DML操作
--DML操作请求--->提交给instead of触发器工作 DML操作在提交给instead of触发器就结束了,DML只负责请求,不负责修改
--instead of触发器会中断DML操作的执行
-----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> grant dba to HYD; --授予视图权限——grant create view to HYD;
Grant succeeded
SQL> create view v_tab2 as select * from tab2;
----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> set serveroutput on;
SQL> insert into v_tab2 values(1,'a');
instead of触发器工作了
1 row inserted
SQL> select * from tab2;
TABID TABNAME
----------- --------------------此时原表没有数据
SQL> select * from v_tab2;
TABID TABNAME
----------- --------------------此时视图表也没有数据
----------------------------------------------------------------------------------------------------------------------------------------------------------
SQL> insert into v_tab2 values(1,'a');
instead of触发器工作了
1 row inserted
SQL> select * from tab;
TABID TABNAME
----------- --------------------
2 a
3 b
4 c
6 触发器工作
SQL> insert into v_tab2 values(1,'a');
instead of触发器工作了
1 row inserted
SQL> select * from tab;
TABID TABNAME
----------- --------------------
2 a
3 b
4 c
6 触发器工作
8 触发器工作
10 触发器工作
12 触发器工作
----------------------------------------------------------------------------------------------------------------------------------------------------------
create or replace trigger tri_tab2
instead of insert on v_tab2
for each row
declare
-- local variables here
begin
dbms_output.put_line('instead of触发器工作了');
insert into tab values(sq_tab.nextval,'触发器工作');
--此处不能插入到tab2,否则会造成死循环
end tri_tab2;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
instead of触发器的好处是:视图中如果是复杂的sql多表查询,可以用:new去插入数据
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
建立系统事件触发器
禁止某些ip地址访问数据库就可以做到,系统触发器一般是管理员做的事情。
如after logon on database 登录数据库之后
after startup on database 启动数据库之后
可以做一些审计表,比如都有谁登录过
----------------------------------------------------------------------------------------------
管理触发器 可以查看用户里头都有什么触发器,状态是什么,enadled是可以使用
select trigger_name from user_triggers;
我们也可以禁止触发器,加快数据装载速度,也可以激活触发器。
可以禁止,或者激活搜有的触发器
可以重新编译触发器,删除触发器drop