Oracle 触发器：数据库中的“友情使者”，守护数据之道的魔法咒语-CSDN博客

触发器有着一套独特的魔法咒语，也就是他的语法。这个咒语由几个关键部分组成：创建触发器的命令CREATE OR REPLACE TRIGGER，触发器的名字，触发时机（BEFORE或AFTER），触发事件（INSERT、UPDATE或DELETE），以及触发后要执行的魔法动作（BEGIN ... END;）。

以上完整语法如下：

CREATE [OR REPLACE] TRIGGER trigger_name  
[BEFORE | AFTER | INSTEAD OF] trigger_event  
ON {table_name | view_name | DATABASE}  
[FOR EACH ROW]  
[WHEN (condition)]  
DECLARE  
  -- declaration statements  
BEGIN  
  -- trigger body; pl/sql statements  
END;

是不是还不了解来上示例：它来咯~~

假设我们有一个friends表，存储着好友的信息。当有新朋友加入时，我们想让触发器自动发送一封欢迎邮件。那么，我们可以这样书写触发器的魔法咒语：

CREATE OR REPLACE TRIGGER welcome_new_friend  
AFTER INSERT ON friends  
FOR EACH ROW  
BEGIN  
   DBMS_OUTPUT.PUT_LINE('欢迎新朋友：' || :NEW.name || ' 加入我们的星球！');  
END;

在这个魔法咒语中，我们定义了一个名为welcome_new_friend的触发器，它在friends表发生插入操作后触发，并且针对每一行新插入的数据执行输出欢迎语的魔法动作。

情节二：应用场景

除了发送欢迎邮件，触发器还有很多应用场景。例如，

1.当好友离开时，触发器可以自动备份他们的信息；

在数据库星球上，每一位数据精灵的离开都是一件令人惋惜的事情。但有了“友情使者”——触发器，我们可以在好友离开时，自动备份他们的信息，让这份友谊的记忆永远留存。

假设我们有一个friends表，它存储着好友的基本信息，如姓名、年龄和联系方式等。当某个好友从我们的星球上离开时，我们希望触发器能够自动备份他们的信息到一个名为former_friends的表中。

首先，我们需要创建former_friends表来存储备份信息：

CREATE TABLE former_friends (  
    id NUMBER PRIMARY KEY,  
    name VARCHAR2(50),  
    age NUMBER,  
    contact_info VARCHAR2(100),  
    departure_date DATE DEFAULT SYSDATE  
);

接着，我们可以创建一个触发器，当friends表中的好友信息被删除时，该触发器会自动将删除的信息插入到former_friends表中。这里使用AFTER DELETE语句，因为在删除操作后备份信息能够确保备份到的是确实被删除的数据。

以下是触发器的SQL代码实现：

CREATE OR REPLACE TRIGGER backup_departing_friend  
AFTER DELETE ON friends  
FOR EACH ROW  
BEGIN  
   INSERT INTO former_friends (id, name, age, contact_info)  
   VALUES (:OLD.id, :OLD.name, :OLD.age, :OLD.contact_info);  
END;

在这个魔法咒语中，我们定义了一个名为backup_departing_friend的触发器。当friends表发生删除操作时，触发器会被激活。:OLD关键字用于引用被删除行（即即将消失的好友）的原始值。这些值随后被插入到former_friends表中，作为备份。

现在，每当我们在friends表中删除一个好友时，触发器就会自动将这位好友的信息备份到former_friends表中。这样，即使好友离开了我们的星球，他们的信息仍然会被妥善保存，供我们日后回忆和怀念。

需要注意的是，在实际应用中，我们可能还需要考虑其他因素，如事务的一致性、并发控制以及性能影响等。此外，如果friends表中有外键约束或其他依赖关系，我们还需要确保在删除操作前处理好这些依赖，以避免触发数据库错误。

总之，通过触发器的魔法，我们可以轻松实现数据的自动备份功能，让数据库星球上的每一份友谊都得以永恒留存。

2.当好友信息更新时，触发器可以通知其他好友。

在数据库星球上，当好友的信息发生变化时，我们希望能够及时通知其他好友。为了实现这一功能，我们可以使用触发器结合Oracle的数据库邮件功能（如使用UTL_SMTP包或数据库内置的邮件功能，这取决于具体的Oracle配置和许可）。

假设我们有一个friends表，存储着好友的基本信息，以及一个notifications表，用于记录需要发送的通知信息。当某个好友的信息在friends表中更新时，我们希望触发器能够自动在notifications表中插入一条记录，以便后续处理（如发送邮件通知）。

首先，我们创建notifications表来存储通知信息：

CREATE TABLE notifications (  
    id NUMBER PRIMARY KEY,  
    notified_friend_id NUMBER,  
    updated_friend_id NUMBER,  
    updated_field VARCHAR2(50),  
    notification_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP  
);

接下来，我们创建一个触发器，当friends表中的信息被更新时，该触发器会在notifications表中插入一条记录。这里使用AFTER UPDATE语句，因为我们希望在更新操作后发送通知。

以下是触发器的SQL代码实现：

CREATE OR REPLACE TRIGGER notify_friend_update  
AFTER UPDATE OF name, age, contact_info ON friends -- 假设我们只关心name, age, contact_info字段的更新  
FOR EACH ROW  
BEGIN  
   -- 假设我们想要通知所有其他好友，这里简化处理，只插入一条记录作为示例  
   INSERT INTO notifications (id, notified_friend_id, updated_friend_id, updated_field)  
   VALUES (notifications_seq.NEXTVAL, -- 假设notifications_seq是一个序列，用于生成唯一的id  
           :OLD.id, -- 通知其他所有好友，这里使用被更新好友的旧ID作为示例  
           :NEW.id, -- 被更新的好友的新ID  
           CASE   
               WHEN :NEW.name <> :OLD.name THEN 'name'  
               WHEN :NEW.age <> :OLD.age THEN 'age'  
               WHEN :NEW.contact_info <> :OLD.contact_info THEN 'contact_info'  
               ELSE NULL -- 如果没有字段更新，则不插入通知记录  
           END);  
END;

在这个魔法咒语中，我们定义了一个名为notify_friend_update的触发器。当friends表的name、age或contact_info字段发生更新时，触发器会被激活。:OLD和:NEW关键字分别用于引用被更新行更新前后的值。

触发器检查哪些字段的值发生了变化，并在notifications表中插入相应的记录。这里，我们简化了通知的逻辑，实际上在真实场景中，你可能需要遍历所有其他好友，并为每个好友插入一条通知记录。此外，你可能还需要结合数据库邮件功能或外部邮件服务来实际发送通知。

请注意，上述代码仅作为示例，并未包含完整的错误处理和通知发送逻辑。在实际应用中，你需要根据具体的业务需求和Oracle的配置来完善触发器的功能。此外，还需要考虑性能和并发问题，特别是当数据库中有大量更新操作时。

最后，要实现真正的邮件通知功能，你可能需要编写额外的存储过程或外部程序来定期查询notifications表，并发送相应的邮件。这通常涉及到Oracle的邮件发送功能（如UTL_SMTP）或与其他邮件服务（如SMTP服务器）的集成。

这些功能使得触发器成为了数据库星球上不可或缺的“友情使者”。

情节三：优缺点

当然，像所有魔法一样，触发器也有其正反两面。

1. 它的优点在于能够自动化地处理数据变化，减轻我们的负担。

2.但是，如果使用不当，也可能会引发一些不必要的麻烦，比如性能下降或者逻辑错误。

情节四：管理增删查禁启

管理触发器也像管理友情一样需要细心。

a: `CREATE TRIGGER`

我们可以使用CREATE TRIGGER来结交新的触发器朋友，

CREATE [OR REPLACE] TRIGGER trigger_name  
[BEFORE | AFTER | INSTEAD OF] trigger_event  
ON {table_name | view_name | DATABASE}  
[FOR EACH ROW]  
[WHEN (condition)]  
DECLARE  
  -- declaration statements  
BEGIN  
  -- trigger body; pl/sql statements  
END;

b: `DROP TRIGGER`

使用DROP TRIGGER来告别不再需要的朋友，

DROP TRIGGER trigger_name;

c：SELECT `TRIGGER`

同时，我们也可以通过查询数据库的元数据来查看当前存在的触发器及其相关信息，了解他们的性格和喜好。

要查询数据库中已有的触发器，你可以查询数据字典视图，如USER_TRIGGERS、ALL_TRIGGERS或DBA_TRIGGERS。这些视图包含了关于触发器的详细信息。

例如，要查询当前用户拥有的所有触发器，你可以使用：

1.--要查询当前用户拥有的所有触发器

SELECT * FROM USER_TRIGGERS;

2.--要查询数据库中所有的触发器（包括其他用户的触发器），并且具有足够的权限

SELECT * FROM ALL_TRIGGERS;

3.--如果你是DBA或具有相应的权限，你还可以查询DBA_TRIGGERS视图来获取更全面的信息。

--此外，如果你想要查看触发器的具体定义或代码，可以使用DBMS_METADATA.GET_DDL函数或查询--USER_SOURCE、ALL_SOURCE或DBA_SOURCE视图（如果你有足够的权限）。

--例如，使用DBMS_METADATA.GET_DDL查看触发器的DDL（数据定义语言）语句：

SELECT DBMS_METADATA.GET_DDL('TRIGGER', 'trigger_name') FROM DUAL;

请注意，上述查询和修改操作需要适当的权限。如果你不是数据库的所有者或管理员，你可能需要联系你的DBA来执行这些操作。同时，在进行任何修改之前，建议备份相关的数据库对象和数据，以防万一出现意外情况。

d:ENABLE AND DISABLE

1 --启用状态

ALTER TRIGGER trigger_name ENABLE;

2 --禁用状态

ALTER TRIGGER trigger_name DISABLE;

情节五：异常

1.系统异常

那么，当触发器在执行过程中遇到问题时，它会如何处理异常呢？触发器有着一套完善的异常处理机制。当遇到错误或异常情况时，触发器可以捕获这些异常，并根据预设的规则进行处理。比如，它可以记录错误信息、回滚事务，或者触发其他的补偿操作，确保数据的完整性和一致性。

示例如下：

--以下是一些常见的Oracle系统异常以及如何在触发器中捕获和处理它们的SQL代码示例：

1. -- NO_DATA_FOUND：当SELECT INTO语句没有返回任何行时引发。



CREATE OR REPLACE TRIGGER tr_check_employee_before_delete  
BEFORE DELETE ON employees  
FOR EACH ROW  
BEGIN  
    -- 假设我们想在删除员工之前检查员工是否有相关的记录在其他表中  
    SELECT 1 INTO dummy_variable  
    FROM related_table  
    WHERE employee_id = :OLD.employee_id;  
EXCEPTION  
    WHEN NO_DATA_FOUND THEN  
        -- 如果没有找到相关记录，则允许删除  
        NULL;  
    WHEN OTHERS THEN  
        -- 对于其他异常，我们可能想要回滚事务  
        RAISE; -- 重新抛出异常，以便调用者可以处理它  

END;



2. --TOO_MANY_ROWS：当SELECT INTO语句返回多行时引发。

CREATE OR REPLACE TRIGGER tr_check_single_row  
BEFORE INSERT ON some_table  
FOR EACH ROW  
DECLARE  
    v_count NUMBER;  
BEGIN  
    -- 检查某个条件是否只匹配一行  
    SELECT COUNT(*) INTO v_count  
    FROM another_table  
    WHERE some_condition = :NEW.some_column;  
      
    IF v_count > 1 THEN  
        RAISE_APPLICATION_ERROR(-20002, '找到多个匹配行');  
    END IF;  
EXCEPTION  
    WHEN TOO_MANY_ROWS THEN  
        -- 这个异常不应该被触发，因为我们已经通过COUNT检查了行数  
        RAISE_APPLICATION_ERROR(-20003, '不应该发生的异常：找到多行');  
    WHEN OTHERS THEN  
        -- 对于其他异常，回滚事务  
        ROLLBACK;  
        RAISE;  

END;



3.--DUP_VAL_ON_INDEX：当试图插入重复键值时引发，该键值违反了唯一索引或主键约束

CREATE OR REPLACE TRIGGER tr_prevent_duplicate_insert  
BEFORE INSERT ON unique_table  
FOR EACH ROW  
BEGIN  
    -- 检查新插入的值是否违反唯一性约束  
    IF :NEW.unique_column IN (SELECT unique_column FROM unique_table) THEN  
        RAISE_APPLICATION_ERROR(-20004, '尝试插入重复值');  
    END IF;  
EXCEPTION  
    WHEN DUP_VAL_ON_INDEX THEN  
        -- 这个异常不应该被触发，因为我们已经通过查询检查了唯一性  
        RAISE_APPLICATION_ERROR(-20005, '不应该发生的异常：重复键值');  
    WHEN OTHERS THEN  
        -- 对于其他异常，回滚事务  
        ROLLBACK;  
        RAISE;  
END;

2. 自定义异常

此外，我们还可以为触发器自定义异常处理逻辑。当触发器执行过程中遇到特定情况时，我们可以定义自己的异常类型和处理方式，让触发器更加灵活和智能。例如，

a. raise：异常抛出并在 pl/sql exception 块中处理

declare v_num number(5) := -1;
 exp_data_range exception; -- 异常定义
begin if v_num < 0 then
raise exp_data_range; -- 异常抛出
end if;
exception
when exp_data_range then
dbms_output.put_line('数据范围不能为负数！');
end;

b. raise_application_error(error_number, error_message, error_boolean);


DECLARE  
  v_num NUMBER := 0;  
BEGIN  
  -- 假设我们期望v_num的值不为0  
  IF v_num = 0 THEN  
    RAISE_APPLICATION_ERROR(-20001, '变量v_num的值不能为0');  
  END IF;  
  -- 其他逻辑...  
EXCEPTION  
  WHEN OTHERS THEN  
    -- 处理异常，例如打印错误消息  
    DBMS_OUTPUT.PUT_LINE('发生错误: ' || SQLERRM);  
END;

raise_application_error(error_number, error_message, error_boolean);
error_number  : 取值范围： -20000 到 -20999 之间
error_message ：相应的提示信息（< 2048 字节），超出则截取。
error_boolean : 可选项，若为 true： 则新错误将被添加到已经引发的错误列表中。  
                       若为 false（默认）：则覆盖。

情节六：触发器的相关内容（分享）

感兴趣的小伙伴们可以看一下本菜鸟总结的脑图如果有什么不足的地方，还希望请指出谢谢啦

【有道云笔记】触发器(oracle).mindmap

https://note.youdao.com/s/Vzq52Ej9

图如下：

情节七：权限

怎么查看是否有管理的权限

小故事来咯~

故事背景：

在数据库星球上，有一位名为Alice的用户。Alice是星球上的一位重要居民，她负责维护好友信息。最近，星球上发生了一些变化，Alice想知道她是否还有权限对friends表进行增删改查操作。

SQL代码实现：

Alice可以登录到数据库，并执行以下SQL查询来检查她对friends表的权限：






-- 查看Alice对friends表的SELECT权限  
SELECT *  
FROM USER_TAB_PRIVS  
WHERE TABLE_NAME = 'FRIENDS'  
AND PRIVILEGE = 'SELECT';  
  
-- 查看Alice对friends表的INSERT权限  
SELECT *  
FROM USER_TAB_PRIVS  
WHERE TABLE_NAME = 'FRIENDS'  
AND PRIVILEGE = 'INSERT';  
  
-- 查看Alice对friends表的UPDATE权限  
SELECT *  
FROM USER_TAB_PRIVS  
WHERE TABLE_NAME = 'FRIENDS'  
AND PRIVILEGE = 'UPDATE';  
  
-- 查看Alice对friends表的DELETE权限  
SELECT *  
FROM USER_TAB_PRIVS  
WHERE TABLE_NAME = 'FRIENDS'  

AND PRIVILEGE = 'DELETE';

--每个查询都会返回一个结果集，如果Alice具有相应的权限，那么结果集中将包含至少一条记录。如果没有返--回记录，则表示Alice没有该权限。

--请注意，上述查询是针对当前登录用户的（即Alice）。如果数据库管理员或其他用户想要查看其他用户的权--限，他们需要具有足够的权限来查询DBA_TAB_PRIVS视图，而不是USER_TAB_PRIVS。

--DBA查看所有用户权限的示例：

--假设数据库管理员想要查看所有用户对friends表的权限，可以执行以下查询：



-- 查看所有用户对friends表的SELECT权限  
SELECT GRANTEE, PRIVILEGE  
FROM DBA_TAB_PRIVS  
WHERE TABLE_NAME = 'FRIENDS'  
AND PRIVILEGE = 'SELECT';  
  
-- 查看所有用户对friends表的INSERT权限（以此类推UPDATE和DELETE）  
SELECT GRANTEE, PRIVILEGE  
FROM DBA_TAB_PRIVS  
WHERE TABLE_NAME = 'FRIENDS'  

AND PRIVILEGE = 'INSERT';

这些查询将返回所有用户对friends表的SELECT、INSERT、UPDATE和DELETE权限的列表。