What is Trigger in Oracle?

It is reusable oracle plsql program, associated with oracle tables.

Fired implicitly on tables\views on which it is created.

When Database event occurs associated trigger will be invoke to perform operations.

It is not performing through select statement.

Any number of trigger can be create on a table.

Can create maximum 12 combinations on a table.

Advantages:

Help to maintain data integrity.

Automatically backup of data for update\delete operations.

Trace unwanted updates and deletes.

Implement complex business rules which are not possible through constraints.

Validate DML statements before modifications on table.

Level of Trigger Executions:

Row level: An event is trigger on each row updated\deleted\inserted in table.

Statement level: An event is trigger on each statement executed.

What are the different categories of trigger?

DML Triggers

DDL Triggers

Instead of Triggers

System Event Triggers

Compound Triggers

For which action\operation trigger fired?

Insert

Update

Delete

Trigger is fired for below event,

Before Action

After Action

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name [ FOR EACH ROW] Declare
—– Variable declarations
BEGIN
—-trigger body
EXCEPTION
—-exception handling code
END;

Ex.

CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
INSERT INTO VEHICLE VALUES (‘VH010′,’Honda’,’100′,’Kanpur’);
END;