SQL oracle触发前后的区别
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3646110/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
difference before and after trigger in oracle
提问by Jagan
Can somebody explain difference between "before" and "after" trigger in oracle 10g with an example ?
有人可以用一个例子来解释 oracle 10g 中“之前”和“之后”触发器之间的区别吗？
回答by Andrew Moore
First, I'll start my answer by defining trigger: a triggeris an stored procedure that is run when a row is added, modified or deleted.
首先，我将通过定义触发器开始我的回答：触发器是在添加、修改或删除行时运行的存储过程。
Triggers can run BEFOREthe action is taken or AFTERthe action is taken.
触发器可以在采取行动之前或采取行动之后运行。
BEFOREtriggers are usually used when validation needs to take place before accepting the change. They run before any change is made to the database. Let's say you run a database for a bank. You have a table accountsand a table transactions. If a user makes a withdrawal from his account, you would want to make sure that the user has enough credits in his account for his withdrawal. The BEFOREtrigger will allow to do that and prevent the row from being inserted in transactionsif the balance in accountsis not enough.
BEFORE当需要在接受更改之前进行验证时，通常会使用触发器。它们在对数据库进行任何更改之前运行。假设您为一家银行运行一个数据库。你有一张桌子accounts和一张桌子transactions。如果用户从他的账户中提款，您需要确保用户的账户中有足够的信用额度来提款。如果余额不足，BEFORE触发器将允许这样做并防止行被插入。transactionsaccounts
AFTERtriggers are usually used when information needs to be updated in a separate table due to a change. They run after changes have been made to the database (not necessarily committed). Let's go back to our back example. After a successful transaction, you would want balanceto be updated in the accountstable. An AFTERtrigger will allow you to do exactly that.
AFTER当信息因更改而需要在单独的表中更新时，通常会使用触发器。它们在对数据库进行更改后运行（不一定提交）。让我们回到我们后面的例子。成功交易后，您会希望balance在accounts表中进行更新。一个AFTER触发器将允许你这样做。
回答by imbroglio
I'm not completely sure what you're interested in knowing, so I'll keep this fundamental.
我不完全确定你有兴趣了解什么，所以我会保留这个基础知识。
Before Triggers
触发前
- As per the name, these triggers are fired prior to creating the row in the table. Subsequently, since the row has not yet been created you have full access to the :new.table_element field. This allows for data cleansing and uniformity if unwanted/malformed data is attempting to be inserted/updated. This is just a basic example, but you need to utilize the before trigger any time you may require access to the ":new" data.
- 顾名思义，这些触发器在创建表中的行之前被触发。随后，由于尚未创建该行，您可以完全访问 :new.table_element 字段。如果尝试插入/更新不需要的/格式错误的数据，这允许数据清理和统一。这只是一个基本示例，但您需要在任何需要访问 ":new" 数据的时候使用 before 触发器。
After Triggers
触发后
- Since the after trigger fires once the row has already been created, these triggers are typically utilized when you want logic to occur due to the row. For example, if you have an address table and a user updates his/her address, then you may want to update the address reference ids in an xref table upon creation (if you happen to be retaining all old addresses as well). Also, unlike the before trigger, you do not have access to modify any of the column values since the row already exists in the table.
- 由于一旦创建了行就会触发 after 触发器，因此当您希望由于行而发生逻辑时，通常会使用这些触发器。例如，如果您有一个地址表并且用户更新了他/她的地址，那么您可能希望在创建时更新外部参照表中的地址引用 ID（如果您碰巧还保留了所有旧地址）。此外，与 before 触发器不同，您无权修改任何列值，因为该行已存在于表中。
回答by srimanta sahoo
BEFORE TRIGGER are used when the trigger action should determine whether or not the triggering statements should be allowed to complete .by using BEFORE TRIGGERS user can eliminate unnecessary processing of the triggering statement but,AFTER TRIGGERS are used when the triggering statements should completed before executing the trigger action.
BEFORE TRIGGER 当触发动作决定是否允许触发语句完成时使用 BEFORE TRIGGERS。通过使用 BEFORE TRIGGERS 用户可以消除对触发语句的不必要处理，但是，当触发语句在执行之前应该完成时使用 AFTER TRIGGERS触发动作。
