当前位置：文江博客话题详情

新列和旧列中的触发器

发布于 2024-08-13 06:58:39 字数 39 浏览 1 评论 0原文

为什么我们不能在语句级触发器中使用 :new 和 :old 列？

原文

分享到QQ

分享到微博

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

发布评论

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

烟酒忠诚 2024-08-20 06:58:39

因为该语句可能会插入/删除/更新不止一行。因此没有新或旧列。

示例：

update FOO set a = 12 where b = 9;

或：

delete from FOO where b = 9;

或：

insert into FOO (a, b) select 12, x from BAR;

如果FOO 表有一个语句触发器，则在这三个句子中无法判断您是对无行、单行还是多行进行操作。

Because it might be the case that the statement is inserting/deleting/updating more than one row. So there is no new or old column.

Example:

update FOO set a = 12 where b = 9;

Or:

delete from FOO where b = 9;

Or:

insert into FOO (a, b) select 12, x from BAR;

If FOO table had a statement trigger, in these three sentences there is no way to tell if you are operating on none, single or multiple rows.

回复收藏 0 原文

夜光 2024-08-20 06:58:39

因为 DML 可能是基于集合的，会影响表中的多行。事实上，由于 SQL 是基于集合的，这应该是通常的情况。因此，语句级触发器无法确定您所指的 :OLD 值和 :NEW 值。

回复收藏 0 原文

丿*梦醉红颜 2024-08-20 06:58:39

如前所述，语句级触发器可以用于一对多行更改，因此 :new 和 :old 不可用。

如果您需要跟踪 :new 和 :old 值并需要在语句触发器上访问它们，您可以创建一个行级触发器来存储新值和旧值以供语句级使用。这是我们之前解决这个问题的一种方法

包：

create or replace package table_trigger_helper is

  subtype subtype_rowtype is table_name$rowtype;
  type table_rowtype is table of subtype_rowtype;

  v_old table_rowtype := table_rowtype();
  v_new table_rowtype := table_rowtype();

end table_trigger_helper;
/

行级触发器：

create or replace trigger row_level_trigger_name
  after insert or delete or update
  on table_name
  for each row
declare

  r_old table_trigger_helper.table_rowtype := NULL;
  r_new table_trigger_helper.table_rowtype := NULL;
  i pls_integer;

begin

 if update or deleting then
  r_old.column_one := :old.column_one
  ...
 end if;

 if update or inserting then
  r_new.column_one := :new.column_one
 end if;

  table_trigger_helper.v_old.extend();
  table_trigger_helper.v_new.extend();
  i := table_trigger_helper.v_old.last;

  table_trigger_helper.v_old( i ) := r_old;
  table_trigger_helper.v_new( i ) := r_new;  
end row_level_trigger_name;
/

语句级触发器：

create or replace trigger statement_level_trigger_name
 after insert or delete or update
 on table_name
declare
begin
  --process through your new and old records;
  --table_trigger_helper.v_old
  --table_trigger_helper.v_new
end statement_level_trigger_name;
/

As said before statement level triggers can be for one to many row changes so :new and :old aren't available.

If you need to track the :new and :old values and need access to them at the statement trigger you can create a row level trigger that stores the new and old values for use by the statement level. Here is one way we have solved this problem before

The package:

create or replace package table_trigger_helper is

  subtype subtype_rowtype is table_name$rowtype;
  type table_rowtype is table of subtype_rowtype;

  v_old table_rowtype := table_rowtype();
  v_new table_rowtype := table_rowtype();

end table_trigger_helper;
/

The row level trigger:

create or replace trigger row_level_trigger_name
  after insert or delete or update
  on table_name
  for each row
declare

  r_old table_trigger_helper.table_rowtype := NULL;
  r_new table_trigger_helper.table_rowtype := NULL;
  i pls_integer;

begin

 if update or deleting then
  r_old.column_one := :old.column_one
  ...
 end if;

 if update or inserting then
  r_new.column_one := :new.column_one
 end if;

  table_trigger_helper.v_old.extend();
  table_trigger_helper.v_new.extend();
  i := table_trigger_helper.v_old.last;

  table_trigger_helper.v_old( i ) := r_old;
  table_trigger_helper.v_new( i ) := r_new;  
end row_level_trigger_name;
/

The statement level trigger:

create or replace trigger statement_level_trigger_name
 after insert or delete or update
 on table_name
declare
begin
  --process through your new and old records;
  --table_trigger_helper.v_old
  --table_trigger_helper.v_new
end statement_level_trigger_name;
/

回复收藏 0 原文

~没有更多了~