一、触发器的概述

1、什么是触发器

触发器是一种特殊的存储过程，它是一段PL/SQL代码，可以在特定的数据库操作（如INSERT、UPDATE、DELETE）发生时自动执行。触发器可以用来实现数据约束、数据审计、数据复制等功能。

• 触发器是当特定事件出现时自动执行的存储过程
  • 提供审计和日志记录
  • 启用复杂的业务逻辑
• 特定事件可以是执行更新的DML语句和DDL语句
  • 自动生成数据
  • 自定义复杂的安全权限
• 触发器不能被显式调用

2、触发器的类型

Oracle11g中的DML触发器分为三种类型：

• 行级触发器：对于每一行数据的操作都会触发该触发器。
• 语句级触发器：对于每一条SQL语句的操作都会触发该触发器。
• 复合触发器：既可以作为行级触发器，也可以作为语句级触发器。

3、触发器的组成

1. 常见触发器由以下三部分组成

   • 触发器语句（事件）
     • 定义激活触发器的 DML 事件和 DDL 事件
   • 触发器限制
     • 执行触发器的条件，该条件必须为真才能激活触发器
   • 触发器操作（主体）
     • 包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运行

2. 在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、触发器的作用

1. 实现数据约束：通过触发器可以对特定的数据库操作进行限制，从而保证数据的完整性和一致性。

   当使用触发器实现数据约束时，可以在触发器中编写逻辑来检查插入、更新或删除的数据是否符合特定的约束条件。以下是一个简单的示例，演示了如何使用触发器实现数据约束：

   假设我们有一个名为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）触发器会检查薪水是否在有效范围内。如果薪水不在有效范围内，触发器会抛出一个自定义的应用程序错误，从而阻止薪水更新操作。

   现在，当我们尝试更新员工的薪水时，触发器会自动检查薪水是否在有效范围内，如果不符合约束条件，更新操作将被阻止并抛出相应的错误信息。

   这就是一个简单的触发器示例，说明了触发器在实现数据约束功能时的作用。其他的数据约束也可以通过类似的方式在触发器中实现。

   ---

2. 实现数据审计：通过触发器可以记录特定的数据库操作，从而实现数据审计的功能。

   假设我们有一个名为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表中，从而实现了数据审计的功能。

   这就是一个简单的触发器示例，说明了触发器在实现数据审计功能时的作用。其他的触发器功能也可以通过类似的方式实现。

   ---

3. 实现数据复制：通过触发器可以在不同的数据库之间实现数据的复制。

   当使用触发器实现数据复制时，可以在触发器中编写逻辑来将插入、更新或删除的数据复制到另一个表中。以下是一个简单的示例，演示了如何使用触发器实现数据复制：

   假设我们有一个名为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表中，从而实现了数据复制的功能。

   这就是一个简单的触发器示例，说明了触发器在实现数据复制功能时的作用。其他的数据复制也可以通过类似的方式在触发器中实现。

   ---

4. 实现业务逻辑：通过触发器可以实现特定的业务逻辑，从而简化应用程序的开发和维护。

   假设我们有一个名为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表中插入新的订单记录时，触发器会自动更新对应客户的最近订单日期，从而实现了业务逻辑的功能。

   这就是一个简单的触发器示例，说明了触发器在实现业务逻辑时的作用。其他的业务逻辑也可以通过类似的方式在触发器中实现。

   ---

5. 实现性能优化：通过触发器可以对特定的数据库操作进行优化，从而提高系统的性能。

   触发器可以用于实现性能优化，例如在数据更新时进行一些计算或者更新其他相关的数据。以下是一个示例代码，演示了如何使用触发器实现性能优化：

   假设我们有一个名为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;
/

下面是对上述语法中各个部分的详细介绍：

1. CREATE [OR REPLACE] TRIGGER trigger_name：用于创建一个新的触发器。OR REPLACE关键字表示如果同名的触发器已经存在，则替换掉原有的触发器。

2. {BEFORE | AFTER | INSTEAD OF}：指定触发器的触发时机，BEFORE表示在操作执行前触发，AFTER表示在操作执行后触发，INSTEAD OF用于视图触发器。

3. {INSERT | UPDATE | DELETE | {INSERT OR UPDATE} | {UPDATE OR DELETE} | {INSERT OR DELETE} | {INSERT OR UPDATE OR DELETE}}：指定触发器要响应的操作类型。可以是单个操作类型，也可以是多个操作类型的组合。

4. ON table_name：指定触发器要绑定的表名。

5. [REFERENCING {NEW AS new | OLD AS old | NEW AS new OLD AS old}]：用于在触发器逻辑中引用新旧值。NEW表示新值，OLD表示旧值。

6. [FOR EACH ROW | FOR EACH STATEMENT]：指定触发器是行级触发器还是语句级触发器。FOR EACH ROW表示行级触发器，FOR EACH STATEMENT表示语句级触发器。

7. [WHEN (condition)]：可选项，用于在行级触发器中指定触发条件。

8. BEGIN ... END;：在BEGIN和END之间包含了触发器的逻辑代码。

9. 触发器逻辑：在BEGIN和END之间的部分是触发器的逻辑代码，用于定义触发器要执行的操作。

10. /：表示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中，可以通过以下方式对触发器进行基本操作：启用、禁用、删除、查看触发器的基本信息。

1. 启用触发器：

   ALTER TRIGGER trigger_name ENABLE;
   

2. 禁用触发器：

   ALTER TRIGGER trigger_name DISABLE;
   

3. 删除触发器：

   DROP TRIGGER trigger_name;
   

4. 查看触发器的基本信息：

   SELECT trigger_name, table_name, triggering_event
   FROM user_triggers;
   

   这条查询语句将返回当前用户拥有的所有触发器的名称（trigger_name）、所属表名（table_name）和触发事件（triggering_event）的信息。

通过以上操作，可以对Oracle 11g中的触发器进行启用、禁用、删除以及查看基本信息的操作。

点击此处跳转下一节：21.Oracle的程序包(Package)