通过触发器在Oracle 11g更新后更新值 [英] Updating value via trigger AFTER UPDATE Oracle 11g
问题描述
我正在开发一个小型图书馆数据库，并且我不想允许某人更新某人的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;
谢谢！
推荐答案
使用以下代码进行触发. 已完成的更改:
Use the below code for trigger. Changes done:
- 使用更新前而不是更新后.
-
将ID的值设置为以前的值. (ID字段将永远不会被修改)
- 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;
CREATE OR REPLACE TRIGGER ALTER_ID_TRIGGER
BEFORE UPDATE ON CUSTOMER
BEGIN
SET :NEW.ID = :OLD.ID
END;
注意::使用 更新前:
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.
这篇关于通过触发器在Oracle 11g更新后更新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
