Oracle DML触发器和DDL触发器实例详解

对于“Oracle DML触发器和DDL触发器实例详解”的攻略，我将从以下几个方面进行详细讲解：

1. 概述
2. DML触发器
3. DDL触发器
4. 示例说明

1. 概述

在Oracle数据库中，触发器是一种特殊的程序，它可以在事件发生时自动执行一系列的操作。一般情况下，触发器主要分为两种类型：DML触发器和DDL触发器。

DML触发器是在表中进行增删改操作时触发，可以用来进行数据校验、数据同步等操作。而DDL触发器则是在数据库对象发生改变时触发，可以用来进行数据库schema管理、审计等操作。

2. DML触发器

DML触发器分为三种类型：BEFORE、AFTER和INSTEAD OF。在BEFORE类型的DML触发器中，触发器的操作发生在数据插入、更新或删除之前；在AFTER类型的DML触发器中，触发器的操作发生在数据插入、更新或删除之后；在INSTEAD OF类型的DML触发器中，触发器会代替数据插入、更新或删除操作本身。

下面是BEFORE类型的DML触发器的示例代码：

CREATE OR REPLACE TRIGGER emp_before_update
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
BEGIN
    :NEW.sal := :NEW.sal * 1.1; -- 增加10%的工资
END;

在上面的触发器中，当emp表中“sal”列的数据被更新时，BEFORE类型的触发器会在更新操作执行之前被触发，并将工资增加10%。

3. DDL触发器

DDL触发器主要用于管理数据库的schema，例如创建、修改和删除表、索引、触发器等对象时触发。

当使用DDL语句创建、修改或删除一个对象时，Oracle数据库会首先检查是否已经创建一个与该DDL语句相关的触发器并进行响应操作。如果相关的触发器存在，则会在DDL语句完成之前自动执行触发器中定义的一些操作。

下面是创建DDL触发器的示例代码：

CREATE TRIGGER ddl_trig
    AFTER DDL ON SCHEMA 
BEGIN
    INSERT INTO ddl_audit(user,ddl_type,ddl_object,ddl_time)
    VALUES
    (USER,ORA_DICT_OBJ_TYPE,ORA_DICT_OBJ_NAME,SYSDATE);
END;

在上面的触发器中，当在Oracle数据库中任何对象上执行了DDL语句之后，DDL触发器会在DDL操作执行完成后自动触发，并将操作记录到“ddl_audit”表中。

4. 示例说明

接下来，我将通过两个不同的示例来展示DML触发器和DDL触发器的具体使用方法。

4.1 示例1：DML触发器

假设我们有一个名为“employee”的表，里面包含员工的姓名和年龄，我们需要一个触发器，当我们向该表中插入一条数据时，触发器可以自动将年龄加上10岁。

触发器的创建可以按照以下步骤进行：

1. 创建employee表

CREATE TABLE employee (
    name VARCHAR2(50),
    age NUMBER(3)
);

1. 创建触发器

CREATE OR REPLACE TRIGGER add_age
BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
    :NEW.age := :NEW.age+10;
END;

在上述代码中，我们先使用CREATE TABLE语句创建了一个名为“employee”的表，然后创建了一个BEFORE INSERT类型的触发器，当我们向该表中插入一条数据时，触发器会在数据插入之前被自动触发，并将新插入的数据的年龄加上10岁。

1. 插入数据

INSERT INTO employee (name,age) VALUES ('张三', 22);

在插入数据之后，我们可以使用SELECT语句来查看插入的数据是否被正确修改：

SELECT * FROM employee;

输出结果为：

NAME   AGE
-----  ---
张三   32

从结果中可以看出，年龄增加了10岁，说明BEFORE INSERT类型的触发器已经被正确地触发。

4.2 示例2：DDL触发器

假设我们需要在每次创建一个新的表时，向另一个表中添加一行信息，记录该表的创建时间、创建者和表名。

触发器的创建可以按照以下步骤进行：

1. 创建记录表

CREATE TABLE table_audit (
    user_name VARCHAR2(50),
    table_name VARCHAR2(50),
    create_time DATE
);

1. 创建DDL触发器

CREATE OR REPLACE TRIGGER table_create_trig
AFTER CREATE
ON SCHEMA
BEGIN
    INSERT INTO table_audit (user_name, table_name, create_time)
    SELECT USER, ORA_DICT_OBJ_NAME, SYSDATE
    FROM DUAL;
END;

在上述代码中，我们首先使用CREATE TABLE语句创建了一个名为“table_audit”的记录表，然后创建了一个AFTER CREATE类型的DDL触发器，当我们在数据库中创建一个新的表时，触发器会在表创建完成后自动触发，并将创建时间、创建者和表名插入“table_audit”表中。

1. 创建新表

CREATE TABLE test_table (
    id NUMBER(10),
    name VARCHAR2(50)
);

在我们创建了一个名为“test_table”的表之后，我们可以使用以下SELECT语句来查看我们记录的信息是否被正确插入：

SELECT * FROM table_audit;

输出结果为：

USER_NAME    TABLE_NAME    CREATE_TIME
-----------  ------------  -----------
TEST         TEST_TABLE    2021-07-15 09:43:16

从结果中可以看出，我们记录的信息已经被正确地插入了“table_audit”表中，说明AFTER CREATE类型的DDL触发器已经被正确地触发。