Oracle SQL在列更新时触发 [英] Oracle SQL trigger on update of column

问题描述

我正在尝试为我的表创建一个触发器，该触发器基于某个标志设置为"Y"的时间自动添加发布日期.

I'm trying to create a trigger for my table which automatically adds a published date based on when a certain flag is set to 'Y'

我在创建触发器方面经验不足，但是到目前为止，这是我所拥有的

I don't have much experience with creating triggers but so far this is what i've got

  create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  BEGIN
  :new.create_dt := sysdate where approved = 'Y';
  END;

更新列时出现此错误

触发"USER.ADD_CREATE_DT"无效且重新验证失败

trigger 'USER.ADD_CREATE_DT' is invalid and failed re-validation

有什么想法吗?

谢谢

推荐答案

使用WHEN子句:

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  when (new.approved = 'Y')
  BEGIN
  :new.create_dt := sysdate;
  END;

或使用IF:

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  BEGIN
  if :new.approved = 'Y' then
   :new.create_dt := sysdate;
  end if;
  END;

在这种情况下，WHEN更合适，更有效.

In this case, WHEN is more appropriate and efficient.

这篇关于Oracle SQL在列更新时触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！