 |
| Using the PL/SQL Compiler |
1. What Is a Compound Trigger?
A single trigger on a table that allows you to specify actions for each of the following four timing points:
a. Before the firing statement.
b. Before each row that the firing statement affects.
c. After each row that the firing statement affects.
d. After the firing statement.
02. Write down The Benefits of Using a Compound Trigger.
You can use compound triggers to:
a. Program approaches where you want the actions you implement for the various timing points to share common data.
b. Accumulate rows destined for a second table so that you can periodically bulk-insert them.
c. Avoid the mutating-table error (ORA-04091) by allowing rows destined for a second table to accumulate and then bulk-inserting them.
03. Write down the Timing-Point Sections of a Table Compound Trigger
A compound trigger defined on a table has one or more of the following timing-point sections. Timing-point sections must appear in the order shown in the table.
04. Write down the Compound Trigger Restrictions.
Compound Trigger Restrictions:
a. A compound trigger must be a DML trigger and defined on either a table or a view.
b. The body of a compound trigger must be compound trigger block, written in PL/SQL.
c. A compound trigger body cannot have an initialization block; therefore, it cannot have an exception section.
d. An exception that occurs in one section must be handled in that section. It cannot transfer control to another section.
e. :OLD and :NEW cannot appear in the declaration, BEFORE STATEMENT, or the AFTER STATEMENT sections.
f. Only the BEFORE EACH ROW section can change the value of :NEW.
g. The firing order of compound triggers is not guaranteed unless you use the FOLLOWS clause.
05. Write down the Trigger Restrictions on Mutating Tables.
Trigger Restrictions on Mutating Tables
a. A mutating table is:
i. A table that is being modified by an UPDATE, DELETE, or INSERT statement, or
ii. A table that might be updated by the effects of a DELETE CASCADE constraint
b. The session that issued the triggering statement cannot query or modify a mutating table.
c. This restriction prevents a trigger from seeing an inconsistent set of data.
d. This restriction applies to all triggers that use the FOR EACH ROW clause.
e. Views being modified in the INSTEAD OF triggers are not considered mutating.
06. Write down the Creating Database-Event Triggers.
Creating Database-Event Triggers:
a. Triggering user event:
i. CREATE, ALTER, or DROP.
ii. Logging ON or OFF.
b. Triggering database or system event:
i. Shutting down or starting up the database.
ii. A specific error (or any error) being raised.
07. Write down the Benefits of Database-Event Triggers.
Benefits of Database-Event Triggers:
a. Improved data security:
i. Provides enhanced and complex security checks
ii. Provides enhanced and complex auditing
b. Improved data integrity:
i. Enforces dynamic data integrity constraints
ii. Enforces complex referential integrity constraints
iii. Ensures that related operations are performed together implicitly
08. Write down the System Privileges Required to Manage Triggers.
The following system privileges are required to manage triggers:
a. The privileges that enable you to create, alter, and drop triggers in any schema:
i. GRANT CREATE TRIGGER TO ora61
ii. GRANT ALTER ANY TRIGGER TO ora61
iii. GRANT DROP ANY TRIGGER TO ora61
b. The privilege that enables you to create a trigger on the database:
i. GRANT ADMINISTER DATABASE TRIGGER TO ora61
c. The EXECUTE privilege (if your trigger refers to any objects that are not in your schema)
09. Write down the Guidelines for Designing Triggers.
Guidelines for Designing Triggers
a. You can design triggers to:
i. Perform related actions.
ii. Centralize global operations.
b. You must not design triggers:
i. Where functionality is already built into the Oracle server.
ii. That duplicates other triggers.
c. You can create stored procedures and invoke them in a trigger, if the PL/SQL code is very lengthy.
d. Excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in large applications.
0 Comments