如何在语句的触发器内获取受语句影响的行数

2021-12-24 00:00:00 oracle rowcount plsql database-trigger

我有一个语句级触发器，每当对表(称为客户)执行 INSERT UPDATE 或 DELETE 操作时都会触发该触发器.我想显示一条消息(到 DBMS_OUTPUT)，其中包含插入/更新/删除的行数.

I have a statement level trigger that fires whenever INSERT UPDATE or DELETE operations are performed on a table (called customers). I want to display a message (to DBMS_OUTPUT) containing the number of rows that were inserted/updated/deleted.

我只想为每个触发语句发送一条消息，例如'4 行被插入到客户表中'.

I just want one message for each triggering statement, eg '4 rows were inserted into customers table'.

如何从触发器声明的内部访问受触发语句影响的行数，即下面代码中的XXX:

How can I access the number of rows that are affected by the triggering statement from INSIDE the trigger declaration, ie XXX in the code below:

CREATE OR REPLACE TRIGGER customer_changes_trigger_2
AFTER INSERT OR UPDATE OR DELETE ON customers

DECLARE
v_operation   VARCHAR(10);
v_number_rows NUMBER;


BEGIN

v_number := XXX;

IF INSERTING THEN
   v_operation := 'inserted';
END IF;

IF UPDATING THEN
   v_operation := 'updated';
END IF;

IF DELETING THEN
   v_operation := 'deleted';
END IF;

DBMS_OUTPUT.PUT_LINE
          (v_number_rows|| ' rows were ' || v_operation || ' from customers.');
END;

在文档中找不到任何内容，感谢任何帮助！

Can't find anything in the documentation, any help appreciated!

推荐答案

一种方法是使用全局变量来跟踪行数，因为没有其他方法可以从语句级触发器中获取行数.然后，您将需要三个触发器……一个语句级别在语句运行之前初始化变量，一个行级别为每一行的变量添加一个，一个语句级别使用您希望的行数.首先，设置变量和一些帮助它的程序:

One way is to use a global variable to track the number of rows as there is no other way to get the row count from a statement level trigger. You would then need three triggers... one statement level to initialise the variable before the statement is run, one row level to add one to the variable for each row, one statement level to use the row count however you wish. First, set up the variable and a few procedures to help it:

create or replace package PKG_ROWCOUNT is
  NUMROWS   number;

  procedure INIT_ROWCOUNT;

  procedure ADD_ONE;

  function GET_ROWCOUNT
    return number;
end PKG_ROWCOUNT;
/

create or replace package body PKG_ROWCOUNT as
  procedure INIT_ROWCOUNT is
  begin
    NUMROWS := 0;
  end;

  procedure ADD_ONE is
  begin
    NUMROWS := Nvl(NUMROWS, 0) + 1;
  end;

  function GET_ROWCOUNT
    return number is
  begin
    return NUMROWS;
  end;
end PKG_ROWCOUNT;
/

初始化变量的第一个触发器:

The first trigger to initialise the variable:

create or replace trigger CUSTOMER_CHANGES_TRIGGER_1
  before insert or update or delete
  on CUSTOMERS
begin
  PKG_ROWCOUNT.INIT_ROWCOUNT;
end;

每行更新的第二个:

create or replace trigger CUSTOMER_CHANGES_TRIGGER_2
  after insert or update or delete
  on CUSTOMERS
  for each row
begin
  PKG_ROWCOUNT.ADD_ONE;
end;
/

第三个显示总数:

create or replace trigger CUSTOMER_CHANGES_TRIGGER_3
  after insert or update or delete
  on CUSTOMERS
begin
   Dbms_output.
   PUT_LINE(PKG_ROWCOUNT.GET_ROWCOUNT || ' rows were affected.');
end;

相关文章