Packages & Triggers – ORACLE PL/SQL Chapter Wise Interview Questions
What is a package spec and package body? Why the separation?
- Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs
- Separation helps make development easier
- Dependency is simplified. You can modify body without in validating dependent objects.
What are the advantages of Packages?
- Encapsulation of code logic
- Privileges to objects can be controlled
- Loaded once into memory, used subsequently.
- Dependency simplified
- Public/private procs, functions, variables
What are Triggers? How to invoke a Trigger on demand?
Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table. Triggers can’t be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, and DELETE) happens on the table on which they are defined. Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.
What are the different types of Triggers?
There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and row level and statement level triggers
BEFORE INSERT FOR EACH ROW
BEFORE UPDATE FOR EACH ROW
BEFORE DELETE FOR EACH ROW
AFTER INSERT FOR EACH ROW
AFTER UPDATE FOR EACH ROW
AFTER DELETE FOR EACH ROW
What is the maximum number of statements that can be specified in a trigger statement?
Can views be specified in a trigger statement?
What are the values of: new and: old in Insert/Delete/Update Triggers?
INSERT; new = new value, old = NULL
DELETE: new = NULL, old = old value
UPDATE: new = new value, old = old value
What are Cascading Triggers? What is the maximum no of Cascading Triggers at a time?
When a statement in a Trigger body causes another Trigger to be fired, the Triggers are said to be Cascading. Max = 32.
What are Mutating Triggers?
A Trigger giving a SELECT on the table on which the Trigger is written.
What are Constraining Triggers?
A Trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.
What is the maximum number of triggers, can apply to a single table?
Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.
Give a PL/SQL command to delete a Trigger?
SQL>DROP TRIGGER trigger_name;