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

本文介绍了通过触发器在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.