Module 09: Creating Compound, DDL, and Event Database Triggers

Creating Compound, DDL, and Event Database  Triggers

01. What Are Triggers?

Triggers:

a. A trigger is a PL/SQL block that is stored in the database and fired (executed) in response to a specified event.

b. The Oracle database automatically executes a trigger when specified conditions occur.

02. Write down the Defining Triggers.

A trigger can be defined on the table, view, schema (schema owner), or database (all users).

03. Write down the types of Trigger Event.

Types of Trigger Event:

a. A database manipulation (DML) statement (DELETE, INSERT, or UPDATE).

b. Database definition (DDL) statements (CREATE, ALTER, or DROP).

c. A database operation such as SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN.

04. Write down the Business Application Scenarios for Implementing Triggers.

You can use triggers for:

a. Security.

b. Auditing.

c. Data integrity.

d. Referential integrity.

e. Table replication.

f. Computing derived data automatically.

g. Event logging.

 

05. Write down the Available Trigger Types.

Available Trigger Types:

a. Simple DML triggers: 

i. BEFORE.

ii. AFTER.

iii. INSTEAD OF.

b. Compound triggers.

c. Non-DML triggers:

i. DDL event triggers

ii. Database event triggers

 

06. Write down the Specifying the Trigger Firing (Timing).

You can specify the trigger timing as to whether to run the trigger’s action before or after the triggering statement:

a. BEFORE: Executes the trigger body before the triggering DML event on a table.

b. AFTER: Execute the trigger body after the triggering DML event on a table.

c. INSTEADOF: Execute the trigger body instead of the triggering statement. This is used for views that are not otherwise modifiable.

07. Write down the Statement-Level Triggers Versus Row-Level Triggers.

08. Write down the Using OLD and NEW Qualifiers.

OLD and NEW Qualifiers 

a. When a row-level trigger fires, the PL/SQL run-time engine creates and populates two data structures:

i. OLD: Stores the original values of the record processed by the trigger.

ii. NEW: Contains the new values.

b. NEW and OLD have the same structure as a record declared using the %ROWTYPE on the table to which the trigger is attached.

09. Write down the Status of a Trigger.

A trigger is in either of two distinct modes:

a. Enabled: The trigger runs its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to true (default).

b. Disabled: The trigger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to true.