Triggers in Oracle database

Oracle engine allows users to define procedures that are implicitly execute, when an insert, update or delete operation issued against a table. These procedures are called database triggers.
They are standalone and are fired implicitly by the oracle engine itself.

These database objects or triggers consists of :

• A named database event and
• A PL/SQL block that will execute when the event occur

When a trigger is fired, SQL statement inside the trigger's PL/SQL block can also fire the same or other trigger. This is called 'cascading triggers'.

Triggers has three basic points:

1. Trigger event or statement: It is a sql statement that causes a trigger to be fired. It can be 'INSERT', 'UPDATE', or 'DELETE'statement for specified table.
2. Trigger Restriction: A trigger restriction specifies a Boolean expression that must be TRUE for the trigger to be fired. A Trigger restriction is specified using a WHEN clause
3. Trigger Action: It is the PL/SQL code to be executed when a triggering statement is encountered and any trigger restriction evaluated to TRUE

We can create trigger with the same name as table, procedure or function

Triggers are used to maintain data redundancy in the database

When any DML operation is performed and we want to do something against it we fired trigger

PL/SQL block of Trigger can not contains transaction control sql statement(commit, rollback, savepoint)

Syntax for Creating/updating a Trigger. To update a trigger we use Replace keyword

CREATE OR REPLACE TRIGGER triggerName

{ BEFORE, AFTER }

{ INSERT, DELETE, UPDATE [ of col1, col2.... ] }

ON tableName

[ REFERENCING { OLD AS old, NEW AS new }]

[ FOR EACH ROW [ WHEN condition ] ]

DECLARE

                  variable declaration;

                  constant declaration;

BEGIN

                  PL/SQL statement;

EXCEPTION

                  exception PL/SQL block;

END;

'FOR EACH ROW' designate the trigger to be a Row trigger. if this clause is omitted, the trigger is called Statement trigger

Oracle have below types of triggers:

• ROW Trigger: A row trigger is fired each time a row in the table is affected by the triggering statement. For eg. if and UPDATE statement update multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement
• STATEMENT Trigger: A statement trigger is fired once on behalf of the triggering statement, independent of the number of rows affected by triggering statement(even if no row updated).
• BEFORE v/s AFTER Trigger:When defining a trigger it is necessary to specify the trigger time ie, specifying when the triggering action is to be executed in relation to the triggering statement. BEFORE and AFTER apply to both and the statement trigger. 

So with above statement, I can say that oracle have 12 type of triggers

1. Before Insert Row trigger
2. Before Update Row trigger
3. Before Delete Row trigger
4. Before Insert Statement trigger
5. Before Update Statement trigger
6. Before Delete Statement trigger
7. After Insert Row trigger
8. After Update Row trigger
9. After Delete Row trigger
10. After Insert Statement trigger
11. After Update Statement trigger
12. After Delete Statement trigger

Delete trigger:
DROP TRIGGER triggerName

Below line of code will show the status of trigger
SELECT STATUS triggerName

Below line  will Enable or Disable trigger
ALTER TRIGGER triggerName DISABLE/ENABLE
this will enable or disable trigger specified with triggerName only

To enable or disable all trigger use below command
ALTER TABLE tableName ENABLE/DISABLE all trigger