在更新 Oracle 11g 后通过触发器更新值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24254466/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Updating value via trigger AFTER UPDATE Oracle 11g
提问by Henrique
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?
我正在开发一个小型图书馆数据库，我不想让某人更新某人的 ID。但我需要使用 AFTER UPDATE 和 FOR EACH STATEMENT（有人告诉我这是 Oracle 的默认设置）。因此，基本上，如果有人更新了客户信息并更改了他/她的 ID 或输入错误，触发器会自动将其再次更新为旧值。问题是在使用 FOR EACH STATEMENT 时，Oracle 不允许我使用 :NEW 和 :OLD。这个问题有什么解决方法吗？
CREATE OR REPLACE TRIGGER alter_id_trigger
AFTER UPDATE ON CUSTOMER
BEGIN
UPDATE CUSTOMER SET ID = :OLD.ID
WHERE ID = :NEW.ID;
END;
Thank you!
谢谢！
回答by Nishanthi Grashia
Use the below code for trigger. Changes done:
使用以下代码进行触发。 所做的更改：
- Using BEFORE UPDATE instead of AFTER UPDATE.
Setting the value of ID to what it was previously. (The ID Field would never be modified)
CREATE OR REPLACE TRIGGER ALTER_ID_TRIGGER BEFORE UPDATE ON CUSTOMER BEGIN SET :NEW.ID = :OLD.ID END;
- 使用 BEFORE UPDATE 而不是 AFTER UPDATE。
将 ID 的值设置为之前的值。（永远不会修改 ID 字段）
CREATE OR REPLACE TRIGGER ALTER_ID_TRIGGER BEFORE UPDATE ON CUSTOMER BEGIN SET :NEW.ID = :OLD.ID END;
Note:With BEFORE UPDATE:
注：随着BEFORE UPDATE：
- You can not create a BEFORE trigger on a view.
- You can updatethe :NEW values.
- You can not updatethe :OLD values.
- 您不能在视图上创建 BEFORE 触发器。
- 您可以更新:NEW 值。
- 您不能更新:OLD 值。
回答by Gordon Linoff
I think you want a beforeupdate trigger:
我想你想要一个更新前的触发器：
CREATE OR REPLACE TRIGGER alter_id_trigger
BEFORE UPDATE ON CUSTOMER
BEGIN
SET :NEW.ID = :OLD.ID
END;
You could test to see if the value is being changed, but that seems unnecessary.
您可以测试该值是否正在更改，但这似乎没有必要。
