PLSQL - Trigger

1. Trigger:

• Triggers are stored programs, which are automatically executed or fired.
• When some event occurs.

       
   Uses:

• preventing invalid transactions.
• imposing security.
• auditing.

  Trigger allowed commit statement?

• Commit, save point, and rollback are not allowed within the trigger body.
• COMMIT - to save the changes.
• ROLLBACK - to roll back the changes.
• SAVEPOINT - creates points within the groups of transactions in which to ROLLBACK.
• It is possible to commit or roll back indirectly calling a procedure, but it is not recommended because of side effects to transactions.
• The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a transaction.
• PRAGMA refers to a compiler directive. The directive restricts member subprograms to querying or modifying database tables and packaged variables.
• An autonomous transaction is an independent transaction of the main or parent transaction.
• If an Autonomous transaction is started by another transaction it is not nested, but independent of the parent transaction.

 AUTONOMUS TRANSACTIONS:

• An autonomous transaction is an independent transaction started by another transaction that is usually called the main transaction.
• Autonomous transactions may issue various DML statements and commit or roll them back.
• Trigger created earlier that fires after the UPDATE or DELETE statement is issued on the <my table> table where you record auditing data.
• Suppose you want to record auditing data even when the main transaction fails.
• The child transaction is called an autonomous transaction.
• An autonomous transaction is a completely independent transaction.
• An autonomous transaction does not share resources, locks, or any commit dependencies with the main transaction.
• Autonomous transactions are useful for creating software components that can be reused in numerous applications.
• A child table is commit are effect.

declare
       pragma_autonomous_transaction;

AUDITING:

• Auditing is the on-site verification activity, such as inspection or examination, of a process or quality system, to ensure compliance with requirements. An audit can apply to an entire organization or might be specific to a function, process, or production step.