difference before and after trigger in oracle

Question

Can somebody explain difference between "before" and "after" trigger in oracle 10g with an example ?

Answer 1

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.

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.

Answer 2

First, I'll start my answer by defining trigger: a trigger is an stored procedure that is run when a row is added, modified or deleted.

Triggers can run BEFORE the action is taken or AFTER the action is taken.

BEFORE triggers 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 accounts and 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 BEFORE trigger will allow to do that and prevent the row from being inserted in transactions if the balance in accounts is not enough.

AFTER triggers 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 balance to be updated in the accounts table. An AFTER trigger will allow you to do exactly that.