在更新 Oracle 11g 后通过触发器更新值

2021-12-30 00:00:00 sql oracle11g oracle triggers

我正在开发一个小型图书馆数据库，我不想让某人更新某人的 ID.但我需要使用 AFTER UPDATE 和 FOR EACH STATEMENT(有人告诉我这是 Oracle 的默认设置).因此，基本上，如果有人更新了客户信息并更改了他/她的 ID 或输入错误，触发器会自动将其再次更新为旧值.问题是在使用 FOR EACH STATEMENT 时，Oracle 不允许我使用 :NEW 和 :OLD.这个问题有什么解决方法吗?

I'm developing a small library database and I don't want to allow someone to update someone's ID. But I need to use AFTER UPDATE and FOR EACH STATEMENT (which I'm told is Oracle's default). So, basically, if someone updates the customer info and alter his/her ID or mistypes it, the trigger will automatically update it again to the old value. The problem is that Oracle won't let me use :NEW and :OLD when using FOR EACH STATEMENT. Are there any workarounds to this issue? 

CREATE OR REPLACE TRIGGER alter_id_trigger
AFTER UPDATE ON CUSTOMER
BEGIN
   UPDATE CUSTOMER SET ID = :OLD.ID
   WHERE ID = :NEW.ID;
END;

谢谢！

推荐答案

使用以下代码触发.已完成更改:

  1. 使用 BEFORE UPDATE 而不是 AFTER UPDATE.

  2. 将 ID 的值设置为之前的值.(永远不会修改 ID 字段)

  1. Using BEFORE UPDATE instead of AFTER UPDATE.

  2. Setting the value of ID to what it was previously. (The ID Field would never be modified)

创建或替换触发器 ALTER_ID_TRIGGER在客户更新之前开始设置 :NEW.ID = :OLD.ID结束;

注意:更新前:一>

Note: With BEFORE UPDATE:

  • 您不能在视图上创建 BEFORE 触发器.
  • 您可以更新 :NEW 值.
  • 您无法更新 :OLD 值.
  • You can not create a BEFORE trigger on a view.
  • You can update the :NEW values.
  • You can not update the :OLD values.

相关文章