Oracle AFTER INSERT trigger.

Preamble

In this tutorial you will learn how to create an AFTER INSERT trigger (after inserting) in Oracle with syntax and examples.

Oracle executes (excites) the AFTER INSERT trigger after executing the INSERT operator.

Syntax of the AFTER INSERT trigger

CREATE [ OR REPLACE ] TRIGGER name_trigger
AFTER INSERT
ON table_name
[ FOR EACH ROW ]
DECLARE
- variable declaration
BEGIN
- trigger code
EXCEPTION
WHEN ...
- emergency handling
END;

Parameters or arguments

• Trigger_name – The name of the trigger to be created.
• AFTER INSERT – indicates that the trigger is triggered after the INSERT operator is executed.
• table_name – The name of the table for which the Trigger was created.

Restrictions

• You cannot create a trigger in views.
• You cannot update :NEW (new) values.
• You cannot update :OLD (old) values.

Example

Let’s take an example of how to create an AFTER INSERT trigger using the CREATE TRIGGER design.
If you have created a table of the following structure:

CREATE TABLE orders
(order_id number(5),
number(4),
cost_per_item number(6,2),
total_cost number(8,2)
);

Then we’ll create the AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
ON orders
FOR EACH ROW
DECLARE
v_username varchar2(10);
BEGIN .
-- Find the person who's running INSERT on the table.
SELECT user INTO v_username
FROM dual;
-- Insert a row in the audit table
INSERT INTO orders_audit
( order_id,
Ah,
cost_per_item,
total_cost,
username )
VALUES
( :new.order_id,
:new.quantity,
:new.cost_per_item,
:new.total_cost,
v_username );
END;

About Enteros

​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​