Stored Objects  «Prev  Next»
Lesson 1

Creating a trigger in Oracle PL/SQL

This module covers a tool that designers sometimes overlook when creating a database system: the database trigger.
The database trigger can add functionality to the database that might otherwise need to be handled by specialized coding.
By the end of this module, you will know how to:
  1. Distinguish between a trigger and a procedure
  2. Describe the syntax and use of special record variables
  3. Create an insert, update, or delete trigger
  4. Build and execute a trigger that covers insert, update, and delete
You will get lots of practice coding PL/SQL in this module.
The next lesson defines the trigger.


A trigger is a named PL/SQL unit that is stored in the database and run in response to an event that occurs in the database. You can specify the event, whether the trigger fires before or after the event, and whether the trigger runs for each event or for each row affected by the event. For example, you can create a trigger that runs every time an INSERT statement affects the EMPLOYEES table.

SQL Query and Processing

Running a SQL query and processing the result set is as easy in PL/SQL as opening a text file and processing each line in popular scripting languages. Using PL/SQL to access metadata about database objects and handle database error conditions, you can write utility programs for database administration that are reliable and produce readable output about the success of each operation. Many database features, such as triggers and object types, make use of PL/SQL. You can write the bodies of triggers and methods for object types in PL/SQL.

Higher Productivity

PL/SQL lets you write very compact code for manipulating data. In the same way that scripting languages such as Perl can read, transform, and write data from files, PL/SQL can query, transform, and update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-engineering features, such as exception handling, encapsulation, data hiding, and object-oriented datatypes. PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger, instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.