qlsql 学习实例代码4

最新推荐文章于 2025-08-11 19:37:52 发布

mutourenoo

最新推荐文章于 2025-08-11 19:37:52 发布

阅读量157

点赞数

分类专栏： golang 文章标签： SQL Oracle 数据结构

golang 专栏收录该内容

15 篇文章

订阅专栏

本文详细介绍Oracle数据库中的触发器使用方法，包括行触发器、语句级触发器、自增触发器及替换触发器等。通过具体示例展示了如何创建及应用不同类型的触发器，如阻止特定操作的触发器和维护日志记录的触发器。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---------------------------行触发器
SQL> create or replace trigger del_deptno ---创建触发器
2 after delete on dept ---定义动作
3 for each row ---行触发
4 begin
5 delete from emp where deptno =:old.deptno; -----触发器要执行的语句
6 end del_deptno;
7 /

Trigger created

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

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
1 liwu

12 rows selected
---------------------------------------------------------
两个重要的内存表存在的情况 (old表和操作的表结构一致，new表是内存中存放数据的一张表)

insert new
delete old
update old new

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1 liwu
8000 john java 6000.00 10

16 rows selected

SQL> create or replace trigger update_dept --创建更新触发器
2 after update on dept
3 for each row
4 begin
5 update emp set deptno =:new.deptno where deptno =:old.deptno; ----牵扯到 new old 两张表
6 end update_dept;
7 /

Trigger created

SQL> update dept set deptno =11 where deptno=10;

1 row updated

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
11 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 11
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 11
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 11
1 liwu
8000 john java 6000.00 11

16 rows selected
------------------------------------------------触发器中的不能写commit rollback,那触发器如何实现对一些操作的阻止呢？

SQL> create or replace trigger del_dept
  2  after delete on dept
  3  for each row
  4  begin
  5  if :old.deptno = 20 then
  6  raise_application_error(-20000,'不允许删除');    ----强行报错语句    -20000到-20999，可随意写
  7  end if;
  8  end del_dept;
  9  /

Trigger created

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
11 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> delete from dept where deptno =11; ----删除其他成功

1 row deleted

SQL> delete from dept where deptno = 20; ----删除20 不成功报错

delete from dept where deptno = 20

ORA-20000: 不允许删除?ORA-06512: at "SYSTEM.DEL_DEPT", line 3
ORA-04088: error during execution of trigger 'SYSTEM.DEL_DEPT'

------------------------------------------------------------语句级触发器
SQL> desc mylog;
Name Type Nullable Default Comments
--------- ------------- -------- ------- --------
CURR_USER VARCHAR2(100) Y
CURR_DATE DATE Y
ACT CHAR(1) Y

SQL> create or replace trigger dml_dept ----创建语句级触发器
2 after insert or delete or update on dept ------触发的动作
3 begin
4 if inserting then
5 insert into mylog values(user,sysdate,'I');
6 elsif deleting then
7 insert into mylog values(user,sysdate,'D');
8 else
9 insert into mylog values(user,sysdate,'U');
10 end if;
11 end dml_dept;
12 /

Trigger created

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into dept values(50,'AAA','aaa');

1 row inserted

SQL> update dept set dname='BBB' where deptno=50;

1 row updated

SQL> delete from dept where deptno = 50;

1 row deleted

SQL> select * from mylog;

CURR_USER CURR_DATE ACT
-------------------------------------------------------------------------------- ----------- ---
SYSTEM 2010-4-4 15 I
SYSTEM 2010-4-4 15 U
SYSTEM 2010-4-4 15 D

--------------------------------------利用触发器进行自增

SQL> create or replace trigger set_no
  2  before insert on dept
  3  for each row
  4  declare
  5  sn number(5);
  6  begin
  7  select myseq.nextval into sn from dual;
  8  :new.deptno := sn;
  9  end set_no;
 10  /

Trigger created

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into dept values(1,'A','d'); ----插入时写的deptno已经不起作用了，而是按照触发器中的序列进行自增。

1 row inserted

SQL> insert into dept values(50,'A','d');

1 row inserted

SQL> insert into dept values(60,'A','d');

1 row inserted

SQL> select * from dept;

DEPTNO DNAME LOC
------ -------------- -------------
69 A d
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
72 A d
66 A d

6 rows selected
------------------------------------------------------替换触发器（在oracle中替换触发器只能建在视图上）

SQL> insert into v_emp_dept values(7903,'wangsu'2,'B','bbb'); ---v_emp_dept为两表关联的查询的试图

insert into v_emp_dept values(7903,'wangsu'2,'B','bbb') ---不能插入视图

ORA-00917: missing comma

SQL> create or replace trigger tr_v_e_d         -----创建替换触发器 来解决此问题
  2  instead of insert on v_emp_dept
  3  for each row
  4  begin
  5  insert into dept(deptno,dname,loc) values(:new.deptno,:new.dname,:new.loc);
  6  insert into emp(empno,ename,deptno) values(:new.empno,:new.ename,:new.deptno);
  7  end tr_v_e_d;
  8  /

Trigger created

SQL> insert into v_emp_dept values(7093,'ws',2,'B','b');

1 row inserted