Oracle Database Triggers classification

DB Triggers categories by 3 types,

Based on 

1. Timing

2. Event

3. Action

Timing Based Triggers

1. Before

2. After

3.Instead of ( only used for complex views)

Event Based Triggers

1. DDL(Create, Alter, Drop)

2. DML(Insert, Delete, Update)

3. DataBase (After Start up, Before Shutdown)

Action Based Triggers

1. Row Level

2. Statement Level

Totally 2*3*2 = 12 Triggers are there,

1. Before Insert Row

2. Before Insert Statement

3. Before Update Row

4. Before Update  Statement

5. Before Delete Row

6. Before Delete  Statement

7. After Insert Row

8. After Insert  Statement

9. After Update Row

10. After Update  Statement

11. After Delete Row

12. After Delete  Statement

 Instead of trigger can be used for perform DML Operations in complex views(Which is created based on more than one table)

Normally we can not use rollback, commit inside the triggers.

But we can use rollback and commit for a sub transactions which is not affect the main transaction.

for ex

CREATE TRIGGER TRIGGER_X

ON TABLE_X

ROW LEVEL

BEGIN

--------------------------------------------------

------------------------SOME CODES----------

--------------------------------------------------

INSERT INTO TABLE_Y

VALUES( X,Y,Z,);

COMMIT;

--------------------------------------------------

------------------------SOME CODES----------

--------------------------------------------------

END TRIGGER_X;