通过触发器在Oracle 11g更新后更新值 [英] Updating value via trigger AFTER UPDATE Oracle 11g

查看:443
本文介绍了通过触发器在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:

  1. 使用更新前而不是更新后.
  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)

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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆