Oracle的触发器

Oracle 触发器

在使用A用户增加或删除表数据时，希望同时在B用户的相应表中增加或删除相同的数据。为实现这一需求，可以使用Oracle数据库中的触发器（Trigger）。

关键概念

• FOR EACH ROW: 指定触发器在每行数据操作时触发。
• :OLD.字段名: 获取操作前的字段值。
• :NEW.字段名: 获取操作后的字段值。

样例代码

CREATE OR REPLACE TRIGGER trigger_name
  AFTER INSERT OR UPDATE OR DELETE ON A_USER.table_name
  FOR EACH ROW
BEGIN
  CASE
    WHEN INSERTING THEN
      IF INSTR(:NEW.ID, 'ab') > 0 THEN
        INSERT INTO B_USER.table_name (column1, column2, column3)
        VALUES (:NEW.column1, :NEW.column2, :NEW.column3);
      END IF;
    WHEN UPDATING THEN
      IF INSTR(:NEW.column1, 'ab') > 0 THEN
        UPDATE B_USER.table_name
           SET column2 = :NEW.column2
         WHERE column1 = :NEW.column1;
      END IF;
    WHEN DELETING THEN
      IF INSTR(:OLD.column1, 'ab') > 0 THEN
        DELETE FROM B_USER.table_name
              WHERE column1 = :OLD.column1;
      END IF;
  END CASE;
END;

注释版本

CREATE OR REPLACE TRIGGER 触发器名称(英文的)
  AFTER update or insert or delete on A用户.表名称 -- 在更新/插入/删除A用户表的时候
  for each row

begin
 
  case
    when inserting then -- 在插入的时候
      IF instr(:NEW.ID,'ab') >0 THEN -- 使用if做进一步筛选 条件可以自己设置
       insert into B用户.表名称
        (字段1,字段2,字段3)
      VALUES
        (:NEW.字段1,:NEW.字段2,:NEW.字段3);
      END IF; -- 结束if
    when updating then -- 在更新的时候
    IF instr(:NEW.字段1,'ab') >0 THEN
      update B用户.表名称
         SET 字段2 = :NEW.字段2
       WHERE 字段1 = :NEW.字段1;
    END IF;	-- 结束if
    when deleting then -- 在删除的时候
    IF instr(:old.字段1,'ab') >0 THEN -- 需要注意的是 删除的时候建议使用:old.字段名
      delete B用户.表名称
       WHERE 字段1 = :old.字段1;
    END IF;	-- 结束if
  end case;	-- 结束case when
end; -- 结束触发器

问题解决

问题1: 触发器中不能包含COMMIT或ROLLBACK

在触发器中使用 COMMIT或 ROLLBACK会导致错误。触发器应在数据库事务中保持一致，不应自行提交或回滚事务。

问题2: 编译时报错，表或视图不存在

在SQLPlus中可以访问B用户的表，但在存储过程中报错，原因是Oracle的PL/SQL块（如过程、函数、包）中对数据的访问权限与SQLPlus不同。

• 原因: PL/SQL块中不能通过角色隐式授权访问其他用户的表。
• 解决方法: 需要A用户显式地被授予权限访问B用户的表。

-- 由B用户执行
GRANT SELECT, INSERT, UPDATE, DELETE ON table_name TO A_USER;

确保A用户具有访问B用户表的显式权限。

进一步优化建议

1. 错误处理: 在触发器中添加异常处理，以应对可能的错误情况。
2. 性能考虑: 避免在触发器中执行耗时操作，以免影响数据库性能。
3. 权限管理: 确保用户权限设置合理，避免安全风险。

通过以上方法，可以有效地使用Oracle触发器实现跨用户表的数据同步。