oracle基础系统学习目录
01.CentOS7静默安装oracle11g 02.Oracle的启动过程 03.从简单的sql开始 04.Oracle的体系架构 05.Oracle数据库对象 06.Oracle数据备份与恢复 07.用户和权限管理 08.Oracle的表 09.Oracle表的分区 10.Oracle的同义词与序列 11.Oracle的视图 12.Oracle的索引 13.Oracle通过JDBC连接Java 14.Oracle中的事务 15.Oracle11g的归档方式和日志文件的相关操作 16.Oracle的数据字典和动态性能视图 17.Oracle11g的PL/SQL基础 18.Oracle的过程和函数 19.Oracle11g中的游标 20.Oracle11g中的触发器 21.Oracle的程序包(Package) 22.Oracle中的临时表空间 23.Oracle11g的UNDO表空间 24.Oracle11g的逻辑备份与恢复 25. Oracle的回收站 26.Oracle11g的数据装载 27.Oracle11g的闪回Flashback 28.Oracle11g物化视图
****@toc
一、触发器的概述
1、什么是触发器
触发器是一种特殊的存储过程，它是一段PL/SQL代码，可以在特定的数据库操作（如INSERT、UPDATE、DELETE）发生时自动执行。触发器可以用来实现数据约束、数据审计、数据复制等功能。
触发器是当特定事件出现时自动执行的存储过程
提供审计和日志记录启用复杂的业务逻辑 特定事件可以是执行更新的DML语句和DDL语句
自动生成数据自定义复杂的安全权限 触发器不能被显式调用
2、触发器的类型
Oracle11g中的DML触发器分为三种类型：
行级触发器：对于每一行数据的操作都会触发该触发器。语句级触发器：对于每一条SQL语句的操作都会触发该触发器。复合触发器：既可以作为行级触发器，也可以作为语句级触发器。
3、触发器的组成
常见触发器由以下三部分组成
触发器语句（事件）
定义激活触发器的 DML 事件和 DDL 事件 触发器限制
执行触发器的条件，该条件必须为真才能激活触发器 触发器操作（主体）
包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行 在Oracle 11g中，触发器由以下几个主要组成部分构成
触发器名称（Trigger Name）
触发器的名称是唯一的，用于标识触发器。在创建触发器时，需要为触发器指定一个名称，并且在数据库中触发器的名称是唯一的。 触发时机（Triggering Event）
触发器可以在不同的操作时机被触发，包括BEFORE、AFTER和INSTEAD OF。BEFORE表示在操作执行之前触发，AFTER表示在操作执行之后触发，INSTEAD OF用于视图触发器。 触发的操作类型（Triggering Action）
触发器可以响应不同类型的数据库操作，包括INSERT、UPDATE和DELETE等。此外，还可以使用组合形式，比如INSERT OR UPDATE、UPDATE OR DELETE等。 触发的表（Triggering Table）
触发器可以绑定到特定的数据库表上，当表上的操作符合触发条件时，触发器会被激活。 触发器逻辑（Trigger Logic）
触发器逻辑定义了触发器被激活时所执行的操作。这部分通常包含在BEGIN和END之间，可以包括各种PL/SQL语句和逻辑。 引用新旧值（Referencing New/Old Values）
在行级触发器中，可以使用NEW和OLD关键字来引用被操作的行的新旧值。这样可以在触发器逻辑中对新旧值进行比较和操作。 触发条件（Trigger Condition）
在行级触发器中，可以使用WHEN关键字来指定触发条件，只有当条件满足时触发器才会被激活。 触发器类型（Trigger Type）
触发器可以是行级触发器（FOR EACH ROW）或者语句级触发器（FOR EACH STATEMENT）。行级触发器会在每行被操作时触发，而语句级触发器会在每个SQL语句执行时触发。 以上是Oracle 11g触发器的主要组成部分，了解这些组成部分可以帮助我们更好地理解和设计触发器，以满足特定的业务需求。
4、触发器的作用
实现数据约束：通过触发器可以对特定的数据库操作进行限制，从而保证数据的完整性和一致性。 当使用触发器实现数据约束时，可以在触发器中编写逻辑来检查插入、更新或删除的数据是否符合特定的约束条件。以下是一个简单的示例，演示了如何使用触发器实现数据约束： 假设我们有一个名为employee的表，包含以下字段：employee_id, employee_name, salary。我们希望在每次更新员工的薪水时，自动检查薪水是否在有效范围内（例如，不能低于最低工资，不能高于最高工资）。 首先，我们创建一个触发器，当有员工的薪水被更新时，自动检查薪水是否在有效范围内。 CREATE OR REPLACE TRIGGER check_salary_range_trigger
BEFORE UPDATE OF salary ON employee
FOR EACH ROW
BEGIN
IF :NEW.salary < 1000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be less than 1000');
ELSIF :NEW.salary > 100000 THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot exceed 100000');
END IF;
END;
/
在上面的代码中，我们创建了一个名为check_salary_range_trigger的触发器。它是一个行级触发器（FOR EACH ROW），当员工的薪水被更新前（BEFORE UPDATE OF salary ON employee）触发器会检查薪水是否在有效范围内。如果薪水不在有效范围内，触发器会抛出一个自定义的应用程序错误，从而阻止薪水更新操作。 现在，当我们尝试更新员工的薪水时，触发器会自动检查薪水是否在有效范围内，如果不符合约束条件，更新操作将被阻止并抛出相应的错误信息。 这就是一个简单的触发器示例，说明了触发器在实现数据约束功能时的作用。其他的数据约束也可以通过类似的方式在触发器中实现。
实现数据审计：通过触发器可以记录特定的数据库操作，从而实现数据审计的功能。 假设我们有一个名为employee的表，包含以下字段：employee_id, employee_name, salary。我们希望在每次有新员工加入时，自动记录员工加入的时间。 首先，我们创建一个新的表employee_audit，用于记录员工加入的时间。表的结构如下： CREATE TABLE employee_audit (
employee_id NUMBER,
join_date DATE
);
接下来，我们创建一个触发器，当有新员工加入时，自动将员工的ID和加入时间插入到employee_audit表中。 CREATE OR REPLACE TRIGGER employee_join_trigger
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (employee_id, join_date)
VALUES (:NEW.employee_id, SYSDATE);
END;
/
在上面的代码中，我们创建了一个名为employee_join_trigger的触发器。它是一个行级触发器（FOR EACH ROW），当有新的员工被插入（AFTER INSERT ON employee）时，触发器会将员工的ID和加入时间插入到employee_audit表中。 现在，当我们向employee表中插入新的员工记录时，触发器会自动将员工的ID和加入时间记录到employee_audit表中，从而实现了数据审计的功能。 这就是一个简单的触发器示例，说明了触发器在实现数据审计功能时的作用。其他的触发器功能也可以通过类似的方式实现。
实现数据复制：通过触发器可以在不同的数据库之间实现数据的复制。 当使用触发器实现数据复制时，可以在触发器中编写逻辑来将插入、更新或删除的数据复制到另一个表中。以下是一个简单的示例，演示了如何使用触发器实现数据复制： 假设我们有一个名为employee的表，包含以下字段：employee_id, employee_name, salary。我们希望在每次有新员工加入时，自动将员工的信息复制到另一个表employee_copy中。 首先，我们创建一个新的表employee_copy，用于存储复制的员工信息。表的结构与employee表相同。 CREATE TABLE employee_copy (
employee_id NUMBER,
employee_name VARCHAR2(100),
salary NUMBER
);
接下来，我们创建一个触发器，当有新员工加入时，自动将员工的信息复制到employee_copy表中。 CREATE OR REPLACE TRIGGER employee_copy_trigger
AFTER INSERT ON employee
FOR EACH ROW
BEGIN
INSERT INTO employee_copy (employee_id, employee_name, salary)
VALUES (:NEW.employee_id, :NEW.employee_name, :NEW.salary);
END;
/
在上面的代码中，我们创建了一个名为employee_copy_trigger的触发器。它是一个行级触发器（FOR EACH ROW），当有新的员工被插入（AFTER INSERT ON employee）时，触发器会将员工的ID、姓名和薪水插入到employee_copy表中。 现在，当我们向employee表中插入新的员工记录时，触发器会自动将员工的信息复制到employee_copy表中，从而实现了数据复制的功能。 这就是一个简单的触发器示例，说明了触发器在实现数据复制功能时的作用。其他的数据复制也可以通过类似的方式在触发器中实现。
实现业务逻辑：通过触发器可以实现特定的业务逻辑，从而简化应用程序的开发和维护。 假设我们有一个名为orders的表，包含以下字段：order_id, order_date, total_amount。我们希望在每次有新订单插入时，自动更新客户的最近订单日期。 以下是一个示例代码，演示了如何使用触发器实现这个业务逻辑： 首先，我们创建一个名为customer的表，包含以下字段：customer_id, customer_name, last_order_date。last_order_date字段用于存储客户的最近订单日期。 CREATE TABLE customer (
customer_id NUMBER,
customer_name VARCHAR2(100),
last_order_date DATE
);
接下来，我们创建一个触发器，当有新订单插入时，自动更新客户的最近订单日期。 CREATE OR REPLACE TRIGGER update_last_order_date_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE customer
SET last_order_date = :NEW.order_date
WHERE customer_id = :NEW.customer_id;
END;
/
在上面的代码中，我们创建了一个名为update_last_order_date_trigger的触发器。它是一个行级触发器（FOR EACH ROW），当有新的订单被插入（AFTER INSERT ON orders）时，触发器会自动更新对应客户的最近订单日期。 现在，当我们向orders表中插入新的订单记录时，触发器会自动更新对应客户的最近订单日期，从而实现了业务逻辑的功能。 这就是一个简单的触发器示例，说明了触发器在实现业务逻辑时的作用。其他的业务逻辑也可以通过类似的方式在触发器中实现。
实现性能优化：通过触发器可以对特定的数据库操作进行优化，从而提高系统的性能。 触发器可以用于实现性能优化，例如在数据更新时进行一些计算或者更新其他相关的数据。以下是一个示例代码，演示了如何使用触发器实现性能优化： 假设我们有一个名为order_items的表，包含以下字段：order_id, item_id, quantity, unit_price。我们希望在每次插入或更新订单项时，自动更新订单的总金额。 首先，我们创建一个名为orders的表，包含以下字段：order_id, order_date, total_amount。total_amount字段用于存储订单的总金额。 CREATE TABLE orders (
order_id NUMBER,
order_date DATE,
total_amount NUMBER
);
接下来，我们创建一个触发器，当有新的订单项插入或更新时，自动更新订单的总金额。 CREATE OR REPLACE TRIGGER update_order_total_amount_trigger
AFTER INSERT OR UPDATE ON order_items
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE orders
SET total_amount = total_amount + (:NEW.quantity * :NEW.unit_price)
WHERE order_id = :NEW.order_id;
ELSIF UPDATING THEN
UPDATE orders
SET total_amount = total_amount - (:OLD.quantity * :OLD.unit_price) + (:NEW.quantity * :NEW.unit_price)
WHERE order_id = :NEW.order_id;
END IF;
END;
/
在上面的代码中，我们创建了一个名为update_order_total_amount_trigger的触发器。它是一个行级触发器（FOR EACH ROW），当有新的订单项被插入或更新（AFTER INSERT OR UPDATE ON order_items）时，触发器会自动更新对应订单的总金额。 在触发器中，我们使用了条件语句来区分插入和更新操作，以便在每种情况下都能正确更新订单的总金额。 现在，当我们向order_items表中插入或更新订单项时，触发器会自动更新对应订单的总金额，从而实现了性能优化的功能。 这就是一个简单的触发器示例，说明了触发器在实现性能优化时的作用。其他的性能优化也可以通过类似的方式在触发器中实现。
总之，触发器是Oracle数据库中非常重要的一个功能，它可以帮助我们实现很多有用的功能，并且可以提高系统的性能和可维护性。在使用触发器时，需要注意避免过度使用，以免对系统性能产生负面影响。
二、触发器的创建语法
1、创建语法
在Oracle 11g中，可以使用以下语法来创建触发器：
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE | {INSERT OR UPDATE} | {UPDATE OR DELETE} | {INSERT OR DELETE} | {INSERT OR UPDATE OR DELETE}}
ON table_name
[REFERENCING {NEW AS new | OLD AS old | NEW AS new OLD AS old}]
[FOR EACH ROW | FOR EACH STATEMENT]
[WHEN (condition)]
BEGIN
-- 触发器逻辑
END;
/
下面是对上述语法中各个部分的详细介绍：
CREATE [OR REPLACE] TRIGGER trigger_name：用于创建一个新的触发器。OR REPLACE关键字表示如果同名的触发器已经存在，则替换掉原有的触发器。 {BEFORE | AFTER | INSTEAD OF}：指定触发器的触发时机，BEFORE表示在操作执行前触发，AFTER表示在操作执行后触发，INSTEAD OF用于视图触发器。 {INSERT | UPDATE | DELETE | {INSERT OR UPDATE} | {UPDATE OR DELETE} | {INSERT OR DELETE} | {INSERT OR UPDATE OR DELETE}}：指定触发器要响应的操作类型。可以是单个操作类型，也可以是多个操作类型的组合。 ON table_name：指定触发器要绑定的表名。 [REFERENCING {NEW AS new | OLD AS old | NEW AS new OLD AS old}]：用于在触发器逻辑中引用新旧值。NEW表示新值，OLD表示旧值。 [FOR EACH ROW | FOR EACH STATEMENT]：指定触发器是行级触发器还是语句级触发器。FOR EACH ROW表示行级触发器，FOR EACH STATEMENT表示语句级触发器。 [WHEN (condition)]：可选项，用于在行级触发器中指定触发条件。 BEGIN ... END;：在BEGIN和END之间包含了触发器的逻辑代码。 触发器逻辑：在BEGIN和END之间的部分是触发器的逻辑代码，用于定义触发器要执行的操作。 /：表示SQL语句的结束。
通过以上语法，可以在Oracle 11g中创建各种类型的触发器，用于实现特定的业务逻辑或性能优化。
2、数据库启动触发器
数据库启动触发器是在数据库启动时自动执行的触发器，它可以用于执行一些初始化操作。在Oracle数据库中，可以使用AFTER STARTUP触发器来实现数据库启动触发器。以下是一个简单的示例：
CREATE OR REPLACE TRIGGER startup_trigger
AFTER STARTUP
ON DATABASE
BEGIN
-- 在数据库启动时执行的操作
DBMS_OUTPUT.PUT_LINE('Database has been started');
-- 可以在这里添加其他需要执行的初始化操作
END;
/
在上面的示例中，我们创建了一个名为startup_trigger的触发器，它在数据库启动后执行。在触发器的BEGIN和END之间可以编写需要在数据库启动时执行的操作。
示例如下： 这段SQL包含了两部分操作：
CREATE TABLE event_table (event varchar2(30),
time date);
这部分SQL用来创建一个名为event_table的表，表中包含了事件(event)和时间(time)两个字段。这个表似乎是用来记录不同事件和它们发生的时间。
CREATE OR REPLACE TRIGGER tr_startup
AFTER startup ON DATABASE
BEGIN
INSERT INTO event_table VALUES (ora_sysevent, SYSDATE);
END;
这部分SQL用来创建一个名为tr_startup的触发器，它是一个“after startup”类型的触发器，意味着它会在数据库启动后触发。当数据库启动时，触发器会将当前系统事件(ora_sysevent)和当前时间(sysdate)插入到event_table表中。
这样，当数据库启动时，触发器会自动将相关信息记录到event_table表中，从而实现了记录数据库启动事件的功能。
3、 用户登录触发器：
用户登录触发器是在用户登录到数据库时自动执行的触发器，它可以用于记录用户登录信息、执行安全检查等操作。在Oracle数据库中，可以使用AFTER LOGON触发器来实现用户登录触发器。以下是一个简单的示例：
CREATE OR REPLACE TRIGGER login_trigger
AFTER LOGON
ON DATABASE
BEGIN
-- 在用户登录时执行的操作
DBMS_OUTPUT.PUT_LINE('User ' || USER || ' has logged in');
-- 可以在这里添加其他与用户登录相关的操作
END;
/
在上面的示例中，我们创建了一个名为login_trigger的触发器，它在用户登录后执行。在触发器的BEGIN和END之间可以编写需要在用户登录时执行的操作，例如记录用户登录信息、执行权限检查等。 示例如下：
CREATE TABLE log_table(username varchar2(20),
logon_time date,
logoff_time date,
address varchar2(20));
这部分SQL用来创建一个名为log_table的表，表中包含了用户名(username)、登录时间(logon_time)、登出时间(logoff_time)和地址(address)等字段。
CREATE OR REPLACE TRIGGER tr_logon
AFTER logon ON DATABASE
BEGIN
INSERT INTO log_table
(username, logon_time, address)
VALUES
(ora_login_user, SYSDATE, ora_client_ip_address);
END;
这部分SQL用来创建一个名为tr_logon的触发器，它是一个“after logon”类型的触发器，意味着它会在用户登录后触发。当用户登录时，触发器会将当前登录用户(ora_login_user)、登录时间(sysdate)和客户端IP地址(ora_client_ip_address)插入到log_table表中。
oracle常用系统变量
- Ora_client_ip_address 返回客户端的ip地址
- Ora_database_name 返回当前数据库名
- Ora_login_user 返回登录用户名
- Ora_dict_obj_name 返回ddl操作所对应的数据库对象名
- Ora_dict_obj_type 返回ddl操作所对应的数据库对象的类型
oracle常用系统变量
通过使用数据库启动触发器和用户登录触发器，可以在数据库启动和用户登录时执行一些必要的操作，从而提高数据库的安全性和可靠性。
三、对触发器的基本操作
在Oracle 11g中，可以通过以下方式对触发器进行基本操作：启用、禁用、删除、查看触发器的基本信息。
启用触发器： ALTER TRIGGER trigger_name ENABLE;
禁用触发器： ALTER TRIGGER trigger_name DISABLE;
删除触发器： DROP TRIGGER trigger_name;
查看触发器的基本信息： SELECT trigger_name, table_name, triggering_event
FROM user_triggers;
这条查询语句将返回当前用户拥有的所有触发器的名称（trigger_name）、所属表名（table_name）和触发事件（triggering_event）的信息。
通过以上操作，可以对Oracle 11g中的触发器进行启用、禁用、删除以及查看基本信息的操作。
点击此处跳转下一节：21.Oracle的程序包(Package)
相关文章
发表评论