This Oracle tutorial explains how to create an AFTER INSERT Trigger in Oracle with syntax and examples.
An AFTER INSERT Trigger capacity that Oracle will fire this trigger after the INSERT operation is executed.
The syntax to create an AFTER INSERT Trigger in Oracle/PLSQL is:
CREATE [ OR REPLACE ] TRIGGER trigger_name AFTER INSERT ON table_name [ FOR EACH ROW ] DECLARE -- variable declarations BEGIN -- trigger code EXCEPTION WHEN ... -- exception handling END;
Parameters or Arguments
OR REPLACE Optional. If specified, it lets in you to re-create the trigger is it already exists so that you can change the trigger definition without issuing a DROP TRIGGER statement. trigger_name The title of the set off to create. AFTER INSERT It suggests that the trigger will furnace after the INSERT operation is executed. table_name The title of the desk that the trigger is created on.
You can not create an AFTER set off on a view. You can not replace the :NEW values. You can not replace the :OLD values.
See additionally how to create AFTER DELETE, AFTER UPDATE, BEFORE DELETE, BEFORE INSERT, and BEFORE UPDATE triggers. See additionally how to drop a trigger.
Let’s seem to be at an example of how to create an AFTER INSERT set off using the CREATE TRIGGER statement.
If you had a table created as follows:
CREATE TABLE orders ( order_id number(5), quantity number(4), cost_per_item number(6,2), total_cost number(8,2) );
We ought to then use the CREATE TRIGGER statement to create an AFTER INSERT set off as follows:
TIP: When the usage of SQLPlus, you want to enter reduce on a new line after the trigger. Otherwise, the script won’t execute.
CREATE OR REPLACE TRIGGER orders_after_insert AFTER INSERT ON orders FOR EACH ROW DECLARE v_username varchar2(10); BEGIN -- Find username of person performing the INSERT into the table SELECT user INTO v_username FROM dual; -- Insert record into audit table INSERT INTO orders_audit ( order_id, quantity, cost_per_item, total_cost, username ) VALUES ( :new.order_id, :new.quantity, :new.cost_per_item, :new.total_cost, v_username ); END; /