What are PL/SQL triggers?

by navya
(hyderbad)

What are triggers? Why do we use before and after in triggers?

Oracle PL/SQL triggers are named and (usually) small pieces of code that are run (fired) when a specified event occurs (the triggering event).

The event can be an insert, update or delete on a table in which case the trigger is known as a DML (data manipulation) trigger. DML triggers can be defined at the STATEMENT level so that they fire just once per triggering statement or at the ROW level so that they fire once for every row in the table affected by triggering statement.

Triggers can also be created for DDL (data definition language) events (such as CREATE, DROP, GRANT) at the schema or database level and for database events (such as STARTUP, SHUTDOWN, LOGON, LOGOFF). Larger pieces of code would usually be placed in a stored PL/SQL procedure which is invoked by the trigger.

Common uses for triggers include:

• recording previous values to create history
• logging events
• enforcing business rules that can't be met just by the use of Oracle table and column constraints
• updating the underlying tables of a view
• providing extra security such as preventing data modification outside business hours
• preventing invalid transactions in the database

The reason for using before and after in a PL/SQL trigger is to specify whether the trigger should be run just before the triggering event occurs or immediately afterwards. For example a statement-level BEFORE INSERT trigger on a table would fire as soon as the SQL INSERT command is issued but before any data is inserted into the table, whereas a statement-level AFTER INSERT trigger on a table would fire after the data has been inserted into the table. A row-level BEFORE INSERT trigger would be fired once for each row that is inserted into the table (before the row is inserted). A row-level AFTER INSERT trigger would be fired once for each row that is inserted into the table (after the row is inserted).