Oracle存储过程中触发器和语句的执行顺序 [英] Order of execution of trigger and statements in Oracle stored procedure

问题描述

下面是我的表结构:

Table -Customer
CustomerID Blacklisted Customer Name
101 Y ABC
102 Y DEF

Table -Blacklist
CustomerID BlacklistID Customer Name
101 1011 ABC
102 1012 DEF

Table -Reason
BlacklistID ReasonID Reason Code
1012 02 Rcode2

主表"Customer"用于存储客户信息.如果更新了在客户表中被列为Y的黑名单，则在更新"Customer"表后会触发一个触发器，将记录插入到"Blacklist"表中. 如果，我们认为该客户已列入黑名单，

Main table "Customer" is to store customer information.There is a trigger after update on table "Customer" to insert record in table "Blacklist" if somebody updates the blacklisted as Y in customer table. We consider the customer as blacklisted if ,

• 客户"表中已列入黑名单的列，其值为"Y"，并且.
• 黑名单和原因表中有针对客户的记录

现在我的要求是将来自后端的客户列入黑名单.为此，我正在编写具有以下查询的存储过程:

Now my requirement is to blacklist the customer from backend.For this i am writing stored procedure with below queries:

1. 更新列入黑名单='Y'的客户集，其中customerid ='102';
2. 从黑名单中将blacklistID选择为var_id，其中customerid ='102';
3. 插入原因(BlacklistID，ReasonID，ReasonCode)值(var_ id，111，'RCODE1');

现在要在原因表(第3步)中插入条目，我需要作为外键的BlacklistID，一旦客户表上的触发器被执行，我将获得BlacklistID的值.所以我的困惑是，我可以假设在客户"表更新时触发的触发器将始终在cntrl到达我的INSERT INTO原因(第3步)语句之前被执行.请提出建议.

Now to insert entry in Reason table(step-3),i need BlacklistID which is a foreign key and i will get the value of BlacklistID once the trigger on customer table gets exceuted.So my confusion is, can i assume the trigger on update of 'Customer' table will always get excuted before the cntrl reaches my INSERT INTO reason(step-3) statement. Please suggest.

推荐答案

是.触发器是该语句的一部分.尽管您不能完全确定*)执行同一条语句中的多个触发器的顺序，但是可以确定，当语句本身完成时，它们也会完成.因此，到步骤2时，已经触发了步骤1的所有更新触发器.

Yes. Triggers are part of the statement. Although you cannot be fully certain *) of the order in which multiple triggers in the same statement are executed, you can be certain that they al are done when the statement itself is done. So by the time of step 2, all update triggers of step one have fired.

*)实际上，默认顺序为:

*) Actually, the default order is:

1. 触发之前的声明级别
2. 触发之前的水平
3. 触发后的水平
4. 触发后的声明级别

但是，如果在触发器之前有两个行级别，则默认情况下您不确定这两个命令的执行顺序.但是我从评论中了解到，在Oracle 11中，您甚至可以指定覆盖这些情况的顺序.

But if you have, say, two row level before trigger, by default you cannot be certain in which order those two are executed. But I learned from the comments that in Oracle 11, you can actually specify the order to cover even those cases.

这篇关于Oracle存储过程中触发器和语句的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！