Are you preparing for PL/SQL Interviews? If yes, then we bring you here a list of commonly asked but important PL/SQL interview questions.

PL (Procedural language) is an extension to SQL (Structured Query Language) where a developer can write complex database interactions using procedures, control structures like branching and iteration, modules and functions. It has tight integration with SQL and supports both dynamic and static SQL.

This article covers a list of all the most important and frequently asked PL/SQL interview questions and answers, which will help you to crack any interview.

Most Popular PL/SQL Interview Questions and Answers

Q1 : What is a Trigger?

Ans: Trigger is a named PL/SQL block stored as a stand-alone object in an Oracle database and is implicitly executed when the triggering event occurs.

You can not make a call to a Trigger to fire. They are never executed unless the triggering event occurs.

Q2 : What is a triggering event?

Ans: Triggering event is a database operation associated with either a table, a view, a schema, or the database. The database operations could be,

    • DML statement
    • DDL statement
    • Database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

Q3 : Types of trigger based on triggering event?

Ans: Based on the database operation there could be,

    • DML trigger- Fired on INSERT, UPDATE, DELETE
    • DDL trigger- Fired on CREATE, ALTER, DROP, TRUNCATE, RENAME
    • System trigger- Fired on SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN

Q4 : What are some use cases of Triggers??

Ans: Triggers are commonly used to,

    • Automatically generate derived column values
    • Prevent invalid transactions
    • Enforce complex security authorizations
    • Enforce complex business rules
    • Provide sophisticated auditing
    • Maintain synchronous table replicates

Q5 : Difference between Triggers and Constraints?

Ans: Triggers and constraints have different purposes for why they exist in Oracle database.

Constraints Triggers
Constraints are created to maintain the integrity of the database.
Triggers can be used to implement complex business rules which cannot be implemented using integrity constraints.
Auditing can't be done using constraints.
One of the important use case of triggers is to use for Auditing.
Affect all rows of the table including that already exist when the constraint is enabled.
Only affect those rows added after the trigger is enabled.

Q6 : Difference between Triggers and Stored procedures?

Ans: Procedures and triggers differ in the way, they are invoked. A stored procedure is executed explicitly by issuing procedure call statement from another block.

  • Triggers are implicitly fired (executed) by Oracle when a triggering INSERT, UPDATE, or DELETE statement is issued on the associated table, no matter which user is connected or which application is being used.
  • A trigger can include SQL and PL/SQL statements and can invoke stored procedures. However Stored procedures do not call triggers directly. Stored procedures include DML statements that can invoke triggers.
  • Procedures may have parameters, whereas triggers are not parameterized.

Q7 : What is a DML Trigger?

Ans: DML trigger is a named PL/SQL block stored as a stand-alone object in an Oracle database and is implicitly executed when triggering event (INSERT, UPDATE, or DELETE) occurs.

Triggers in the same schema cannot have the same names. Triggers can have the same names as other schema objects. For example, a table and a trigger can have the same name-however, to avoid confusion, this is not recommended.

Q8 : What happens when a DML is executed on a view and base table has trigger?

Ans: When base table(s) of a view have triggers, then triggers are fired if any triggering DML operation is performed on the view.

Q9 : What happens when a trigger is created with compilation errors and a triggering event occurs?

Ans: Similar to procedures/functions, triggers can be created with compilation errors.

If a trigger has compilation errors it is created and exists in the database. When a triggering DML statement occurs, trigger is fired, but fails on execution, effectively blocks all triggering DML statements until it is disabled, dropped or replaced by a version without compilation errors.

Q10 : Types of DML Triggers in Oracle?

Ans: There are following types of DML triggers in Oracle.

    • Statement level trigger and Row level trigger.
    • Before trigger and after trigger
    • Instead of trigger
    • Compound trigger

Q11 : Difference between Statement and Row level triggers?

Ans: The database fires the FOR EACH ROW trigger for each row that is affected by the triggering statement. Row trigger body can read and write the :OLD and :NEW fields.

Statement triggers are created when you omit FOR EACH ROW keyword. The database fires the statement trigger only once for the triggering statement. Statement trigger body cannot read :NEW or :OLD fields. 

Q12 : Can you create multiple triggers of same type on a table?

Ans: Oracle allows create multiple triggers of the same type (BEFORE, AFTER, or INSTEAD OF) that fire for the same statement on the same table. The order in which Oracle Database fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same statement, then combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.

Q13 : What is the maximum number of triggers, you can apply on a single table?

Ans: 12 triggers with all combination of –

before|after – statement|row level – insert|update|delete

Q14 : What is the PL/SQL trigger execution hierarchy?

Ans: The following execution hierarchy is followed when more than one triggers, are defined on a table.

1) BEFORE statement trigger fires first.
2) Next BEFORE row level trigger fires, once for each row affected.
3) Then AFTER row level trigger fires once for each affected row.
4) Finally the AFTER statement level trigger fires.

NOTE

BEFORE and AFTER row level triggers (Point-2 and 3) will alternate between each other for each row.

Q15 : Create a trigger to insert a row to Audit table, for any row inserted or updated in "Test" table?

CREATE TABLE Test
(
test_id INTEGER,
value NUMBER,
test_date DATE
);

CREATE TABLE Test_audit
(
test_id INTEGER,
value NUMBER,
test_date DATE
);

Ans:

CREATE TRIGGER trg_test
AFTER INSERT OR UPDATE ON Test
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO Test_audit
    VALUES(:NEW.test_id, :NEW.value, :NEW.test_date);
  END IF;
  IF UPDATING THEN
    INSERT INTO Test_audit
    VALUES(:OLD.test_id, :OLD.value, :OLD.test_date);
  END IF;
END trg_test;

Q16 : What is cascade trigger?

Ans: Triggers can contain statements that cause other triggers to fire. Such triggers are called as cascading triggers.

Q17 : What is the limit in PL/SQL for cascading triggers?

Ans: The trigger cascade limit is Operating system dependent and is typically 32.

Q18 : Can you Commit in a FOR EACH ROW Trigger? If 'No', then why? If 'Yes', then why?

Ans : The question is very tricky and hence you need to answer the question also in a tricky way.

If answer is YES,

You can apply COMMIT/ ROLLBACK on triggers by declaring that trigger as AUTONOMOUS_TRANSACTION.

If answer is NO,

Committing in a trigger will violate the integrity of the transaction. E.g.

Committing in a trigger is not allowed because a trigger is part of a larger transaction.  Say you could have a ROW trigger that could commit and you execute something like:

   update T set x = x+1;

Now, that update command will update every single row (or not) as an atomic operation.  If we allowed you to commit in the row trigger — then you might commit HALF of the update, get an error on the other HALF and end up with a HALF updated table. 

That just goes against the entire concept of an atomic transaction.  Identical cases arise for all 4 trigger types — where the transactional consistency of the database would be totally destroyed if you committed.

Q19 : What is the usage of WHEN clause in trigger?

Ans: A WHEN clause specifies the condition that must be true for the trigger to fire.

The NEW and OLD keywords, when specified in the WHEN clause, are not considered bind variables, so are not preceded by a colon (:). However, you must precede NEW and OLD with a colon in all references other than the WHEN clause.

Q20 : What are the restrictions on WHEN clause?

Ans: If you specify WHEN clause in a trigger, then you must also remember the following limitations.

    • Trigger should have at-least one of the timing points – BEFORE EACH ROW | AFTER EACH ROW | INSTEAD OF EACH ROW
    • The WHEN condition cannot include a subquery or a PL/SQL expression (for example, an invocation of a user-defined function)

Q21 : Create a trigger that checks if "value" is greater than 1000 for any new row inserted to "Test" table, then insert a row to Audit table?

CREATE TABLE Test
(
test_id INTEGER,
value NUMBER,
test_date DATE
);

CREATE TABLE Test_audit
(
test_id INTEGER,
value NUMBER,
test_date DATE
);

Ans:

CREATE TRIGGER trg_test
AFTER INSERT ON Test
FOR EACH ROW
WHEN (NEW.value > 1000)
BEGIN
    INSERT INTO Test_audit
    VALUES(:NEW.test_id, :NEW.value, :NEW.test_date);
END trg_test;

Q22 : What is INSTEAD OF trigger?

Ans: Triggers that are associated with a VIEW are called INSTEAD OF Triggers or VIEW Triggers.

A composite view is not directly modifiable. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements. These triggers are named INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the DML statement.

NOTE

    • The INSTEAD OF option can only be used for triggers created over views.

    • The BEFORE and AFTER options cannot be used for triggers created over views.

    • The CHECK option for views is not enforced when inserts or updates to the view are done using INSTEAD OF triggers. The INSTEAD OF trigger body must enforce the check.

Q23 : Why INSTEAD OF triggers are required?

Ans: A simple view is always updatable as it can accept DML operations. In the other hand many complex views are not inherently updatable because they were created with one or more of the constructs listed below:

    • A set operator
    • A DISTINCT operator
    • An aggregate or analytic function
    • A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
    • A collection expression in a SELECT list
    • A sub-query in a SELECT list
    • A sub-query designated WITH READ ONLY
    • Joins, with some exceptions

Any view that contains one of those constructs can be made updatable by using an INSTEAD OF trigger. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through INSERT, UPDATE and DELETE statements. These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle Database fires the trigger instead of executing the DML statement.

With an INSTEAD OF trigger, you can write normal INSERT, UPDATE and DELETE statements against the view, and the INSTEAD OF trigger works invisibly in the background to make the right actions take place.

Q24 : What is mutating trigger error? What are mutating tables?

Ans: Mutating error normally occurs when we are performing some DML operations and we are trying to select the affected record from the same trigger. So basically we are trying to select records in the trigger from the table that owns the trigger. This creates inconsistency and Oracle throws a mutating error.
While dealing with triggers, mutating table is a table that has the possibility of changing. A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.

Q25 : What is Compound trigger?

Ans: Oracle allows create multiple triggers of the same type (BEFORE, AFTER, or INSTEAD OF) that fire for the same statement on the same table. The order in which Oracle Database fires these triggers is indeterminate. A compound trigger is introduced in 11g which combines the following four triggers into one to have seemingly logical behavior.

    • before statement
    • before row
    • after row
    • after statement

The most common reasons for wanting to use compound triggers are:

    • To avoid the mutating table problem
    • To collect the affected rows for processing in batch (e.g. to log them).
Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments