(PLSQL) 在 Oracle 更新触发器中测试更改值的最简单表达式是什么？

发布于 2024-09-18 19:57:17 字数 168 浏览 4 评论 0原文

这是一个可以解决问题的布尔表达式：

nvl(:new.location != :old.location, (:new.location is null) != (:old.location is null))

但我想有一个更简单的表达式。有什么想法吗？

原文

Here is a boolean expression that does the trick:

nvl(:new.location != :old.location, (:new.location is null) != (:old.location is null))

But I would like to think there was a simpler expression. Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

孤独患者 2024-09-25 19:57:18

您可以创建一个像这样的重载包函数：

package p is

    function changed (p_old varchar2, p_new varchar2) return voolean;
    function changed (p_old number, p_new number) return voolean;
    function changed (p_old date, p_new date) return voolean;

end;

然后只需在触发器中调用它：

if p.changed(:old.location,:new.location) then ...

或者您也可以这样做：

if nvl(:old.location,'£$%') != nvl(:new.location,'£$%') then ...

当然，您必须选择一个永远可能是真实位置的值，该值在某些情况下可能会很棘手。对于 VARCHAR2，您可以选择一个对于列大小来说太长的值（除非恰好是 4000）。

You could create an overloaded package function like this:

package p is

    function changed (p_old varchar2, p_new varchar2) return voolean;
    function changed (p_old number, p_new number) return voolean;
    function changed (p_old date, p_new date) return voolean;

end;

Then just call it in your triggers:

if p.changed(:old.location,:new.location) then ...

Alternatively you can just do this:

if nvl(:old.location,'£$%') != nvl(:new.location,'£$%') then ...

Of course, you have to pick a value that could never be a real location, which may be tricky in some cases. For VARCHAR2 you could pick a value that's too long for the column size (unless that happens to be 4000).

回复收藏 0 原文

月竹挽风 2024-09-25 19:57:18

decode(:old.location, :new.location, 1) is null

编辑：这样说可能更清楚：

decode(:old.location, :new.location, null, 1) = 1

decode(:old.location, :new.location, 1) is null

edit: It might be more clear to say:

decode(:old.location, :new.location, null, 1) = 1

回复收藏 0 原文

黎夕旧梦 2024-09-25 19:57:18

也许您只想在值发生变化时触发触发器？如果是这样，您最好在创建触发器时使用以下语法：

CREATE OR REPLACE TRIGGER DepartTrigger BEFORE UPDATE OF location ON Department

Perhaps you only want the trigger to fire at all if the value has changed? If so, you are best to use this syntax when creating the trigger:

CREATE OR REPLACE TRIGGER DepartTrigger BEFORE UPDATE OF location ON Department

回复收藏 0 原文

空名 2024-09-25 19:57:17

这些较短的方法都有几个缺点。
它们很慢，不直观，可能有错误（尽可能避免魔法值），
并且比 AND/OR/IS NULL/IS NOT NULL 等正常条件更具专有性。

NVL、DECODE、COALESCE 等，可能比您想象的更昂贵。

我已经在几个不同的上下文中多次看到过这种情况，这里有一个简单的例子：

--Shorter method: Takes about 0.45 seconds
declare
  j number;
begin
  for i in 1 .. 1000000 loop
    j := i;
    if nvl(i <> j, (i is null) <> (j is null)) then
      null;
    end if;
  end loop;
end;
/

--Normal method: Takes about 0.25 seconds
declare
  j number;
begin
  for i in 1 .. 1000000 loop
    j := i;
    if i <> j or (i is null and j is not null) or (i is not null and j is null) then
      null;
    end if;
  end loop;
end;
/

我建议您多花一点时间以合乎逻辑的方式输入它。您的代码将会看起来更好并且运行得更快。

These shorter methods all have several disadvantages.
They are slow, unintuitive, potentially buggy (avoid magic values whenever possible),
and more proprietary than normal conditions like AND/OR/IS NULL/IS NOT NULL.

NVL, DECODE, COALESCE, etc., can be more expensive than you think.

I've seen this lots of times in several different contexts, here's a simple example:

--Shorter method: Takes about 0.45 seconds
declare
  j number;
begin
  for i in 1 .. 1000000 loop
    j := i;
    if nvl(i <> j, (i is null) <> (j is null)) then
      null;
    end if;
  end loop;
end;
/

--Normal method: Takes about 0.25 seconds
declare
  j number;
begin
  for i in 1 .. 1000000 loop
    j := i;
    if i <> j or (i is null and j is not null) or (i is not null and j is null) then
      null;
    end if;
  end loop;
end;
/

I recommend you spend the extra second to type it the logical way. Your code will look better and run faster.

回复收藏 0 原文

~没有更多了~