目录
定义
触发器事件
触发器（事件）的种类以及它们包括的事件
触发器种类
CREATE TRIGGER
OR REPLACE
EDITIONABLE | NONEDITIONABLE
Restriction on NONEDITIONABLE
DML事件触发器
特点
行级触发器
测试一
条件 对表进行增删改时进行触发
测试二
条件 当修改数值达到条件时触发
测试三
条件 当达到条件时触发报错
级联触发器
测试一
测试二
语句级触发器
测试一
数据库事件触发器
数据库事件触发器分类
数据库级
模式级
数据库事件的属性
测试一
测试二
DDL触发器
替代触发器
ALTER TRIGGER
测试
DROP TTIGGER
定义
使用 PL/SQL 定义触发器。当达到一定条件的时候触发相关的存储过程
若要在可插拔数据库 （PDB） 上创建触发器，当前容器必须是 PDB，并且必须具有管理数据库触发器系统权限。 除了上述权限之外，若要创建交叉触发器，还必须为版本启用。 如果触发器发布 SQL 语句或调用过程或函数，则触发器的所有者必须具有执行这些操作所需的权限。这些特权必须直接授予所有者，而不是通过角色获得。
触发器事件
触发器（事件）的种类以及它们包括的事件
| 种类 | 关键字 | 含义 |
| DML | INSTER | 在表或视图中插入数据时触发 |
| | UPDATE | 在修改表或视图的数据时触发 |
| | ELETE | 在删除表或视图的数据时触发 |
| DDL | CREATE | 在创建新对象时触发 |
| | ALTER | 在修改数据库或数据库对象时触发 |
| | DROP | 在删除对象时触发 |
| 数据库事件 | STARTUP | 启动数据库时触发 |
| | SHUTDOWN | 关闭数据库时触发（Nomal或Immediate） |
| | LOGIN | 当用户链接到数据库并建立会话时触发 |
| | LOGOGG | 当一个会话从数据库中断时触发 |
| | SERVERERROR | 当发生服务器错误时触发 |
触发器种类
| 种类 | 简称 | 作用 |
| 数据库操纵语言触发器 | DML触发器 | 创建在表上，由DML事件引发 |
| 替代触发器 | INSTRAD OF触发器 | 创建在视图上，用来替换对视图进行的插入，删除和修改操作 |
| 数据库定义语言触发器 | DDL触发器 | 定义在模式上，触发事件时数据库对象的创建和修改 |
| 数据库事件触发器 | —— | 定义在整个数据库或模式上，触发事件是数据库事件 |
CREATE TRIGGER
OR REPLACE
指定或替换以重新创建触发器（如果已存在）。使用此子句可以更改现有触发器的定义，而无需先删除它。
EDITIONABLE | NONEDITIONABLE
使用这些子句指定触发器是版本化对象还是非编辑对象默认值为"EDITIONABLE "。
Restriction on NONEDITIONABLE
不能为交叉触发器指定"NONEDITIONABLE"。
DML事件触发器
特点
此触发器定义在表上，由DML操作所触发，即需要定义触发的表和触发的事件
定义触发的事件，是before还是after，表示在DML操作执行之前还是之后触发
定义触发器级别，有语句级和行级触发器，语句级表示此SQL语句只触发一次，行级表示SQL语句影响到的每一行都触发一次
如果一张表上有多个触发器，则其触发事件顺序为：语句级before>行级before>处理数据>行级after>语句级after
注意
如果多个触发器被定义成相同的时间和事件触发，则最后定义的触发器会被触发，其他的不执行
一个触发器可以有不同的DML触发，在触发器中可以使用inserting、updating、deleting词来进行区别，也就是说可以作为if条件的判断
在行级触发器中用:new或:old来访问数据变更前后的值，insert插入一条新纪录，无:old值，delete删除一条记录，无:new值，update更新一条新纪录，既有:new值又有:old值
对于某列值的访问，使用:new字段名或:old字段名
触发器内禁止使用commit、rollback、savepoint语句，禁止直行隐式提交的额相关命令
行级触发器
测试一
条件 对表进行增删改时进行触发
给scott创建触发器的权限
| SQL> conn / as sysdba
Connected.
SQL> grant create trigger to scott;
Grant succeeded.
SQL> conn scott/tiger;
Connected. |
创建一个序列seq_trigger01，用于记录编号
| SQL> create sequence seq_trigger01;
Sequence created. |
创建测试表tb_trigger01使用序列
创建测试表tb_trigger02参照emp
| SQL> create table tb_trigger01(seq_id number(4) primary key,info varchar2(70),write_date date);
Table created.
SQL> create table tb_trigger02 as select * from emp;
Table created. |
创建行级触发器
| SQL> create or replace trigger tr01 before insert or update or delete on tb_trigger02 for each row begin if inserting then insert into tb_trigger01 values( seq_trigger01.nextval, user||' has inserted sal '||:new.sal||' into table tb_trigger02.', sysdate); elsif updating then insert into tb_trigger01 values( seq_trigger01.nextval, user||' has updated sal '||:old.sal||' of table tb_trigger02.', sysdate); else insert into tb_trigger01 values( seq_trigger01.nextval, user||' has deleted sal '||:old.sal||' from table tb_trigger02.', sysdate); end if; end tri01;
/
Trigger created. |
修改tb_trigger02的数据
| SQL> insert into tb_trigger02(empno,ename,sal) values(1111,'aaa','2222');
1 row created.
SQL> update tb_trigger02 set sal='3333' where empno=1111;
1 row updated.
SQL> delete tb_trigger02 where empno=1111;
1 row deleted.
SQL> commit;
Commit complete. |
测试触发器是否生效
| SQL> select * from tb_trigger01;
SEQ_ID INFO WRITE_DATE
------ ----------------------------------------------------- -------------------
1 SCOTT has inserted sal 2222 into table tb_trigger02. 2021-02-18 14:24:27
2 SCOTT has updated sal 2222 of table tb_trigger02. 2021-02-18 14:24:48
3 SCOTT has deleted sal3333 from table tb_trigger02. 2021-02-18 14:25:36 |
如果上述update语句中没有追加where条件的话则会追加多次
| SQL> update tb_trigger02 set sal='3333';
14 rows updated.
SQL> select * from tb_trigger01;
SEQ_ID INFO WRITE_DATE
------ ----------------------------------------------------- -------------------
1 SCOTT has inserted sal 2222 into table tb_trigger02. 2021-02-18 14:24:27
2 SCOTT has updated sal 2222 of table tb_trigger02. 2021-02-18 14:24:48
3 SCOTT has deleted sal 3333 from table tb_trigger02. 2021-02-18 14:25:36
4 SCOTT has updated sal 800 of table tb_trigger02. 2021-02-18 14:30:06
5 SCOTT has updated sal 1600 of table tb_trigger02. 2021-02-18 14:30:06
6 SCOTT has updated sal 1250 of table tb_trigger02. 2021-02-18 14:30:06
7 SCOTT has updated sal 2975 of table tb_trigger02. 2021-02-18 14:30:06
8 SCOTT has updated sal 1250 of table tb_trigger02. 2021-02-18 14:30:06
9 SCOTT has updated sal 2850 of table tb_trigger02. 2021-02-18 14:30:06
10 SCOTT has updated sal 2450 of table tb_trigger02. 2021-02-18 14:30:06
11 SCOTT has updated sal 3000 of table tb_trigger02. 2021-02-18 14:30:06
12 SCOTT has updated sal 5000 of table tb_trigger02. 2021-02-18 14:30:06
13 SCOTT has updated sal 1500 of table tb_trigger02. 2021-02-18 14:30:06
14 SCOTT has updated sal 1100 of table tb_trigger02. 2021-02-18 14:30:06
15 SCOTT has updated sal 950 of table tb_trigger02. 2021-02-18 14:30:06
16 SCOTT has updated sal 3000 of table tb_trigger02. 2021-02-18 14:30:06
17 SCOTT has updated sal 1300 of table tb_trigger02. 2021-02-18 14:30:06
17 rows selected. |
测试二
条件 当修改数值达到条件时触发
创建序列seq_trigger02，用于记录编号
| SQL> create sequence seq_trigger02;
Sequence created. |
创建测试表tb_trigger03使用序列seq_trigger02
创建测试表tb_trigger04参照emp
| SQL> create table tb_trigger03(seq_id number(4) primary key,info varchar2(70),write_date date);
Table created.
SQL> create table tb_trigger04 as select * from emp;
Table created. |
创建行级触发器tr02，当SALESMAN的工资修改幅度超过100时进行记录
| SQL> create or replace trigger tr02 before update of sal on tb_trigger04 for each row begin if(:new.job='SALESMAN' and(abs(:new.sal-:old.sal)>100))then insert into tb_trigger03 values(seq_trigger02.nextval, :new.ename||'''s'||' old_sal: '||:old.sal||' ,'||'new_sal: '||:new.sal, sysdate); end if; end tr02;
/
Trigger created. |
修改表tb_trigger04数据进行测试
先查看表中数据
| SQL> select empno,ename,job,sal from tb_trigger04;
EMPNO ENAME JOB SAL
---------- --------- ---------- -----
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
14 rows selected. |
修改数据
| SQL> update tb_trigger04 set sal=1601 where empno=7499;
1 row updated.
SQL> update tb_trigger04 set sal=9999 where empno=7521;
1 row updated.
SQL> update tb_trigger04 set sal=8888 where empno=7788;
1 row updated.
SQL> commit; |
查看tb_trigger03中记录的数据
| SQL> select * from tb_trigger03;
SEQ_ID INFO WRITE_DATE
------ ----------------------------------------------------- -------------------
1 WARD's old_sal: 1250 ,new_sal: 9999 2021-02-18 14:58:34 |
由于ward的修改幅度超过100因此被记录，其他的不符合条件，所以没有
测试三
条件 当达到条件时触发报错
创建序列seq_trigger03，用于记录编号
| SQL> create sequence seq_trigger03;
Sequence created. |
创建测试表tb_trigger05使用序列seq_trigger03
创建测试表tb_trigger06参照emp
| SQL> create table tb_trigger05(seq_id number(4) primary key,info varchar2(70),write_date date);
Table created.
SQL> create table tb_trigger06 as select * from emp;
Table created. |
创建触发器tr03当SALESMAN的工资低于1000或高于5000时禁止修改，并发出报错
| SQL> create or replace trigger tr03 before update of sal on tb_trigger06 for each row begin if (:new.job='SALESMAN' and (:new.sal<1000 or :new.sal>5000)) then raise_application_error(-20001,'the sal value must between 1000 and 5000!'); else insert into tb_trigger05 values(seq_trigger03.nextval, :new.ename||q'['s old_sal:]'||:old.sal||q'[,new_sal]'||:new.sal,sysdate); end if; end tr03;
/
Trigger created. |
报错号可自定义(-20199到-20001之间)
查看tb_trigger06中的数据
| SQL> select empno,ename,job,sal from tb_trigger06;
EMPNO ENAME JOB SAL
---------- --------- ---------- -----
7369 SMITH CLERK 800
7499 ALLEN SALESMAN 1600
7521 WARD SALESMAN 1250
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7788 SCOTT ANALYST 3000
7839 KING PRESIDENT 5000
7844 TURNER SALESMAN 1500
7876 ADAMS CLERK 1100
7900 JAMES CLERK 950
7902 FORD ANALYST 3000
7934 MILLER CLERK 1300
14 rows selected. |
修改相关数据
| SQL> update tb_trigger06 set sal=100 where empno=7499;
update tb_trigger06 set sal=100 where empno=7499
*
ERROR at line 1:
ORA-20001: the sal value must between 1000 and 5000!
ORA-06512: at "SCOTT.TR03", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR03'
SQL> update tb_trigger06 set sal=7000 where empno=7499;
update tb_trigger06 set sal=7000 where empno=7499
*
ERROR at line 1:
ORA-20001: the sal value must between 1000 and 5000!
ORA-06512: at "SCOTT.TR03", line 3
ORA-04088: error during execution of trigger 'SCOTT.TR03'
SQL> update tb_trigger06 set sal=3000 where empno=7499;
1 row updated. |
查看tb_trigger05中的数据
| SQL> select * from tb_trigger05;
SEQ_ID INFO WRITE_DATE
------ ----------------------------------------------------- -------------------
1 ALLEN's old_sal:1600,new_sal3000 2021-02-18 16:13:08 |
级联触发器
类似存在主键约束关系的数据，当更新或删除父表数据的时候，子表的数据级联收到影响
测试一
实现当修改dept表的deptno时，emp表中的deptno也随之修改
创建emp和dept表的复制表emp_tri和dept_tri
| SQL> create table emp_tri as select * from emp;
Table created.
SQL> create table dept_tri as select * from dept;
Table created. |
查看表中的数据
| SQL> select empno,ename,sal,deptno from emp_tri;
EMPNO ENAME SAL DEPTNO
------ --------- ----- -------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 10
14 rows selected.
SQL> select * from dept_tri;
DEPTNO DNAME LOC
------- ----------- --------
10 ACCOUNTING TJ CHINA
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON |
创建行级after触发器
| SQL>
create or replace trigger tr04
after
update of deptno
on dept_tri
FOR EACH ROW
BEGIN
UPDATE emp_tri SET emp_tri.deptno = :NEW.deptno
WHERE emp_tri.deptno = :OLD.deptno;
END tr04;
/
Trigger created. |
修改dept_pri表中的deptno
| SQL> update dept_tri set deptno=88 where deptno=10;
1 row updated.
SQL> commit;
Commit complete. |
查看数据
| SQL> select * from dept_tri;
DEPTNO DNAME LOC
------- ----------- --------
88 ACCOUNTING TJ CHINA
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select empno,ename,sal,deptno from emp_tri;
EMPNO ENAME SAL DEPTNO
------ --------- ----- -------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7782 CLARK 2450 88
7788 SCOTT 3000 20
7839 KING 5000 88
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 88
14 rows selected. |
测试二
级联删除
创建测试触发器tr05
| SQL>
create or replace trigger tr05
after
delete
on dept_tri
FOR EACH ROW
BEGIN
if DELETING THEN
DELETE FROM emp_tri
where emp_tri.deptno=:old.deptno;
END IF;
END tr05;
/
Trigger created. |
测试删除dept_tri中deptno=88的行
| SQL> delete from dept_tri where deptno=99;
0 rows deleted.
SQL> delete from dept_tri where deptno=88;
1 row deleted. |
查看数据
| SQL> select * from dept_tri;
DEPTNO DNAME LOC
------- ----------- --------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select empno,ename,sal,deptno from emp_tri;
EMPNO ENAME SAL DEPTNO
------ --------- ----- -------
7369 SMITH 800 20
7499 ALLEN 1600 30
7521 WARD 1250 30
7566 JONES 2975 20
7654 MARTIN 1250 30
7698 BLAKE 2850 30
7788 SCOTT 3000 20
7844 TURNER 1500 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
11 rows selected. |
语句级触发器
语句级触发器在出发时，不管操作的行数是多少，只触发一次
适合对整个表操作的权限进行控制
不需要for each row子句
测试一
限定表emp_tri只能在工作日修改（周一至周五的早八点到晚五点）
创建触发器tr06
| SQL>
select * from tb_trigger05;
create or replace trigger tr06
before
insert or update or delete
on emp_tri
begin
if ((to_char(sysdate,'dy') in ('sat','sun'))
or to_char(sysdate,'hh24') <'08'
or to_char(sysdate,'hh24') >'17') then
raise_application_error(-20002,'please modify it on workday');
end if;
end tr06;
/
Trigger created. |
查询日历
修改数据库时间为20日
| SQL> conn / as sysdba
Connected.
SQL> alter system set fixed_date='2020-02-20';
System altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2020-02-20 00:00:00 |
修改emp_tri表
| SQL> conn scott/tiger;
Connected.
SQL> delete from emp_tri;
delete from emp_tri
*
ERROR at line 1:
ORA-20002: please modify it on workday
ORA-06512: at "SCOTT.TR06", line 5
ORA-04088: error during execution of trigger 'SCOTT.TR06' |
测试成功。改回系统时间
| SQL> alter system set fixed_date=none;
System altered.
SQL> select sysdate from dual;
SYSDATE
-------------------
2021-02-19 16:01:45 |
数据库事件触发器
数据库事件触发器分类
数据库级
触发事件为数据库事件，如数据库的启动、关闭、用户的登录、退出等，需要administer database trigger权限
模式级
触发事件包括用户的登录、退出，对数据库对象的创建和修改等，需create trigger 或者create any trigger权限
| 种类 | 关键字 | 说明 |
| 模式级 | CREATE | 创建新对象时触发 |
| | ALTER | 修改数据库或数据库对象时触发 |
| | DROP | 删除对象时触发 |
| 数据库级 | STARTUP | 数据库打开时触发 |
| | SHUTDOWN | 在使用NORMAL或IMMEDIATE选项关闭数据库时触发 |
| | SERVERERROR | 爱服务器错误时触发 |
| 数据库与模式级 | LOGON | 当用户链接到数据库，建立会话时触发 |
| | LOGOFF | 昂会话从数据库中断时触发 |
数据库事件的属性
| 属性 | 使用触发器类型 | 说明 |
| sys.sysevent | 所有类型 | 返回触发器触发事件字符串 |
| sys.instance_num | 所有类型 | 返回Oracle实例号 |
| sys.database_name | 所有类型 | 返回database名字 |
| sys.server_error(stack_position) | SERVERERROR | 从错误堆栈指定位置返回错误号，参数为1表示最近的错误 |
| is_servererror(error_number) | SERVERERROR | 判断堆栈中是否有参数指定的错误号 |
| sys.login_user | 所有类型 | 返回导致触发器触发的用户名 |
| sys.dictionary_obj_type | CREATE,ALTER,DROP | 返回DDL触发器触发时涉及的对象类型 |
| sys.dictionary_obj_name | CREATE,ALTER,DROP | 返回DDL触发器出发时涉及的对象名 |
| sys.des_encriypted_password | CREATE,ALTER,DROP | 创建或修改用户时，返回加密后的用户密码 |
测试一
创建一个触发器，实现本次数据库启动后，对登录的用户及事件进行记录，数据重启后清空该表
创建记录表
| SQL> create table userlogin01(username varchar2(10),login_time date);
Table created. |
创建login触发器
| SQL>
create or replace trigger tr_login01
after
LOGON
on DATABASE
begin
insert into userlogin01 values(sys.login_user,sysdate);
end tr_login01;
/
Trigger created. |
创建数据库startup触发器
| SQL>
create or replace trigger tr_startup01
after
startup
on DATABASE
begin
delete from userlogin01;
end tr_startup01;
/
Trigger created. |
登录用户测试
| SQL> conn scott/tiger;
Connected.
SQL> conn / as sysdba
Connected.
SQL> conn scott/tiger
Connected. |
查看记录表
| SQL> select * from userlogin01 ;
USERNAME LOGIN_TIME
------------------------------ -------------------
TIGER 2021-02-20 11:39:57
SYS 2021-02-20 11:39:51
SCOTT 2021-02-20 11:39:54 |
重启数据库
| SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 763363328 bytes
Fixed Size 8625368 bytes
Variable Size 557843240 bytes
Database Buffers 192937984 bytes
Redo Buffers 3956736 bytes
Database mounted.
Database opened.
SQL> select * from userlogin01 ;
no rows selected |
记录已经被清空
测试二
创建模式级触发器用于记录用户scott的登录事件
创建记录表userlogin02
| SQL> create table userlogin02(username varchar2(10),login_time date);
Table created. |
创建模式触发器tr_scott01
| SQL>
create or replace trigger tr_scott01
after
LOGON
on scott.schema
begin
insert into userlogin02 values(sys.login_user,sysdate);
end tr_scott01;
/
Trigger created. |
用户测试连接
| SQL> conn scott/tiger
Connected.
SQL> conn tiger/scott
Connected.
SQL> conn / as sysdba
Connected. |
查看记录表
| SQL> select * from userlogin02;
USERNAME LOGIN_TIME
------------------------------ -------------------
SCOTT 2021-02-20 12:54:39 |
DDL触发器
可以通过触发器实现阻止某些表的某些操作
测试一
通过触发器阻止删除scott下的emp_bak表
创建emp_bak表
| SQL> create table emp_bak as select * from emp;
Table created. |
创建DDL触发器tr_ddl01
| SQL>
create or replace trigger tr_ddl01
BEFORE
DROP
on scott.schema
begin
if sys.dictionary_obj_name='EMP_BAK' then
raise_application_error(-20004,'table EMP_BAK can not be drop!');
end if;
end tr_ddl01;
/
Trigger created. |
测试操作
| SQL> drop table scott.emp_bak;
drop table scott.emp_bak
*
ERROR at line 1:
ORA-04088: error during execution of trigger 'SYS.TR_DDL01'
ORA-00604: error occurred at recursive SQL level 1
ORA-20004: table EMP_BAK can not be drop!
ORA-06512: at line 3 |
测试其他ddl操作
| SQL> truncate table emp_bak;
Table truncated. |
执行成功，可见此触发器只针对与drop操作
替代触发器
通过替代触发器可以完成一些额外操作
创建测试表emp_instead
| SQL> create table emp_instead as select * from emp;
Table created. |
在emp_instead表中的empno列上添加主键约束
| SQL> alter table emp_instead add constraint PK_EMP_INSTEAD01 primary key(empno);
Table altered.
SQL> select constraint_name,constraint_type,table_name from user_constraints where table_name='EMP_INSTEAD';
CONSTRAINT_NAME CON TABLE_NAME
---------------- --- ---------------
PK_EMP_INSTEAD01 P EMP_INSTEAD |
创建视图v_emp01
| SQL> create view v_emp01 as select ename,sal,deptno from emp_instead;
View created. |
查看视图中的数据
| SQL> select * from v_emp01;
ENAME SAL DEPTNO
--------- ----- -------
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
14 rows selected. |
通过视图向基表emp_instead中添加数据
| SQL> insert into v_emp01 values('aaa',9999,99);
insert into v_emp01 values('aaa',9999,99)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP_INSTEAD"."EMPNO") |
插入失败，原因是基表中empno列含有主键，视图中的数据不是完整的列导致插入的empno为空值
创建一个触发器tr_instead01当向视图v_emp中添加的时候为empno自动填充数值
| SQL>
create or replace trigger tr_instead01
instead of
insert on v_emp01
for each row
declare
v_empno number(4);
begin
select max(empno)+1 into v_empno from EMP_INSTEAD;
insert into EMP_INSTEAD(empno,ename,sal,deptno) values(v_empno, :new.ename, :new.sal, :new.deptno);
end tr_instead01;
/
Trigger created. |
插入测试
| SQL> insert into v_emp01 values('aaa',9999,99);
1 row created.
SQL> select * from v_emp01;
ENAME SAL DEPTNO
--------- ----- -------
SMITH 800 20
ALLEN 1600 30
WARD 1250 30
JONES 2975 20
MARTIN 1250 30
BLAKE 2850 30
CLARK 2450 10
SCOTT 3000 20
KING 5000 10
TURNER 1500 30
ADAMS 1100 20
JAMES 950 30
FORD 3000 20
MILLER 1300 10
aaa 9999 99
15 rows selected.
SQL> select * from emp_instead;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- --------- ---------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
7935 aaa 9999 99
15 rows selected. |
成功追加
ALTER TRIGGER
测试
修改之前的触发器tr_ddl01 drop操作修改为不可用
| SQL> alter trigger tr_ddl01 disable;
Trigger altered. |
drop表emp_bak
| SQL> drop table scott.emp_bak;
Table dropped. |
触发器失效
DROP TTIGGER
删除触发器tri_ddl01
| SQL> drop trigger tr_ddl01;
Trigger dropped. |
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
