Oracle触发器- 替代删除操作，更新行记录

Question

如何编写Oracle触发器，以便当用户删除某个记录时，删除不会真正发生，而是对这些行执行更新，并将记录的状态设置为“D”？

我尝试了以下代码：

create or replace
trigger DELFOUR.T4M_ITEM_ONDELETE
before delete on M_ITEM_H
FOR EACH ROW
BEGIN
  UPDATE
    M_ITEM_H
  SET
    ITEM_STAT = 'D'
  WHERE
    CUST_CODE = 'TEST'
    AND ITEM_CODE = 'GDAY'
  ;

  raise_application_error(-20000,'Cannot delete item');
END;

但是我遇到了可变表错误。这种情况有可能吗？

- Lock

Answer 1

如果你真的需要一个触发器，更合理的方法是创建一个视图，在视图上创建一个INSTEAD OF DELETE触发器，并强制应用程序对视图进行删除，而不是对基本表进行删除。

CREATE VIEW vw_m_item_h
AS
SELECT *
  FROM m_item_h;

CREATE OR REPLACE TRIGGER t4m_item_ondelete
  INSTEAD OF DELETE ON vw_m_item_h
  FOR EACH ROW
AS
BEGIN
  UPDATE m_item_h
     SET item_stat = 'D'
   WHERE <<primary key>> = :old.<<primary key>>;
END;

更好的做法是，不使用触发器，而是创建一个名为delete_item的过程，应用程序将调用该过程而不是发出DELETE语句，该过程仅更新行以设置item_stat列，而不是删除行。

如果您真的非常想在表本身上使用触发器来解决问题，那么可以：

1. 创建一个包，其中包含一个成员，该成员是映射到m_item_h表中数据的记录集合
2. 创建一个在删除之前的语句级别触发器，清空此集合
3. 创建一个在删除之前的行级别触发器，将:old.<<primary key>>和所有其他:old值插入集合中
4. 创建一个在删除之后的语句级别触发器，遍历集合，重新将行插入表中，并设置item_stat列。

这比使用instead of trigger需要更多的工作，因为您需要删除并重新插入行，而且涉及的部分更多，所以它会更加复杂。但是它是可行的。

- Justin Cave

---

很遗憾，我没有访问该应用程序的权限，因此无法更改此内容。 - Lock

@Lock - 你是说这是一个第三方打包的应用程序吗？如果是，为什么你能够首先通过添加触发器来修改它呢？ - Justin Cave

因为我拥有数据库的sysdba访问权限。 - Lock

@Lock - 如果这是一个第三方打包的应用程序，供应商通常不允许您首先向系统添加触发器。您可能从技术上拥有访问该系统以能够添加触发器。但这样做将会违反您的支持合同，除其他事项外。如果您不介意违反支持合同，我会回到创建视图。您可以重命名表格，创建一个视图，使用表格名称，并在视图上创建INSTEAD OF 触发器。这通常对应用程序是透明的。 - Justin Cave

Answer 2

首先，您编写的触发器会引发一个可变表错误。从技术上讲，您所要求的是不可能的，即删除不会真正删除而是更新，除非在中间引发异常，这可能是一种丑陋的做法。我认为用户使用某种应用程序前端，该前端让他们使用“删除”按钮来删除数据，因此您可以在那里使用更新语句而不是“删除”语句。

另一个选择是创建一个日志表，在从实际表中删除记录之前，您可以将该记录插入到日志表中，然后将日志表与实际表连接以检索已删除的记录。类似于-

 CRETAE TABLE M_ITEM_H_DEL_LOG as SELECT * FROM M_ITEM_H WHERE 1=2;

然后

create or replace
trigger DELFOUR.T4M_ITEM_ONDELETE
before delete on M_ITEM_H
FOR EACH ROW
BEGIN
  INSERT INTO
    M_ITEM_H_DEL_LOG
  VALUES (:old.col1, :old.col2,.....) --col1, col2...are columns in M_ITEM_H
  ;
END;

- Anjan Biswas

---

谢谢你的回复！我没有访问该应用程序。基本上，这是一个仓库管理系统，如果该产品没有任何动态，则会删除项目代码。如果有动态，则只是停用该项目。我希望它始终停用该项目。界面使用Oracle Forms，因此它非常丑陋，所以如果用户看到Oracle错误，我不太担心，因为当前应用程序中充斥着这些错误。 - Lock

如果您可以更改Oracle Forms代码以更改删除按钮功能，则可以这样做。否则，可以在raise_application_error中说一些类似于“项目已删除/停用”的消息，而不是“无法删除项目”。 - Anjan Biswas