问题描述
我有一个语句级触发器,每当对表(称为客户)执行 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:
在文档中找不到任何内容,感谢任何帮助!
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:
初始化变量的第一个触发器:
The first trigger to initialise the variable:
每行更新的第二个:
第三个显示总数:
这篇关于如何在语句的触发器内获取受语句影响的行数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!