Config Router

You are here: Home / Interview Questions / Packages & Triggers – ORACLE PL/SQL Chapter Wise Interview Questions

Packages & Triggers – ORACLE PL/SQL Chapter Wise Interview Questions

by

Packages & Triggers – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
What is a package spec and package body? Why the separation?
Answer:

  1. Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs
  2. Separation helps make development easier
  3. Dependency is simplified. You can modify body without in validating dependent objects.

Question 2:
What are the advantages of Packages?
Answer:

  1. Encapsulation of code logic
  2. Privileges to objects can be controlled
  3. Loaded once into memory, used subsequently.
  4. Dependency simplified
  5. Public/private procs, functions, variables

Question 3:
What are Triggers? How to invoke a Trigger on demand?
Answer:
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.

Question 4:
What are the different types of Triggers?
Answer:
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

Row level:
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

Statement level:
BEFORE INSERT
BEFORE UPDATE
BEFORE DELETE
AFTER INSERT
AFTER UPDATE
AFTER DELETE

Question 5:
What is the maximum number of statements that can be specified in a trigger statement?
Answer:
One.

Question 6:
Can views be specified in a trigger statement?
Answer:
No

Question 7:
What are the values of: new and: old in Insert/Delete/Update Triggers?
Answer:
INSERT; new = new value, old = NULL
DELETE: new = NULL, old = old value
UPDATE: new = new value, old = old value

Question 8:
What are Cascading Triggers? What is the maximum no of Cascading Triggers at a time?
Answer:
When a statement in a Trigger body causes another Trigger to be fired, the Triggers are said to be Cascading. Max = 32.

Question 9:
What are Mutating Triggers?
Answer:
A Trigger giving a SELECT on the table on which the Trigger is written.

Question 10:
What are Constraining Triggers?
Answer:
A Trigger giving an Insert/Update on a table having referential integrity constraint on the triggering table.

Question 11:
What is the maximum number of triggers, can apply to a single table?
Answer:
12 Triggers.

Question 12:
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.
Answer:
Yes, database trigger would fire.

Question 13:
Give a PL/SQL command to delete a Trigger?
Answer:

SQL>DROP TRIGGER trigger_name;