oracle自学之触发器

来源：互联网发布：php的发展前景编辑：程序博客网时间：2024/05/21 21:34

--常用：DML触发器，INSTEAD OF触发器（视图触发器）
--触发事件：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

阅读全文

0 0