Oracle PL/SQL Trigger Interview Questions

Most Popular PL/SQL Trigger Interview Questions and Answers

Most job interviews involve an exchange of common question patterns. Therefore it is always good to get prepared before an interview and answer at least some of them with confidence.

Read top 50+ questions and answers on Triggers in Oracle.

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: Triggers in Oracle has following parameters and can be created with any combination of-

2 types based on triggering time – Before or After
2 types based on trigger type – Statement or Row level
3 types based on event type – Insert or Update or Delete

Hence, 2*2*3 = 12 types of triggers are possible with all combination.

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? Provide the reasoning.

Ans : Conceptually committing in a row trigger is not allowed because, a trigger is fired for an event or a parent transaction and commit/rollback has to be decided based on the status of parent transaction, either everything to commit or rollback. Hence, COMMIT within a row trigger will lead to partial data commit case and violates the rule of atomicity of transaction.

For example, there is a FOR EACH ROW update trigger created on table “TEST” that has COMMIT and below statement is executed.

UPDATE Test SET x = x+1;

In this case need to understand following,

The UPDATE statement is triggering event, calls the trigger for each row update and all together constitute single transaction (atomic transaction) has to be either committed or rolled back.
Commit in the row trigger, might result partial data update situation, if any error is encountered during mid of transaction. This might lead to a partial updated table and hence is not allowed for Oracle row triggers.

In some cases like Auditing requirement, if you want to record which table row getting changed then, in that case, once a user issues any DML, then it must be recorded in the audit logs, despite the DML is success or failure. For these scenarios Oracle provides AUTONOMOUS_TRANSACTION to enable COMMIT/ ROLLBACK with ROW LEVEL triggers.

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 : Can you create trigger on Views?

Ans: Yes, only Instead Of triggers.

Q23 : 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.

Q24 : 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.

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

Ans: When something mutates, it is changing. Something that is changing is hard to analyze and quantify. A mutating table is a table that is currently being modified by any DML statement. Tables can also change by the effects of a declarative DELETE CASCADE referential integrity constraint.

A mutating table error (ORA-04091) occurs when a row-level trigger attempts to read or write the same table on which the trigger is created.

Fortunately, the same restriction does not apply in statement-level triggers, which means, Statement level triggers do not raise mutating trigger error.

Q26 : 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).

Q27 : Provide a mutating trigger example?

Ans: Consider a scenario- Allocation of commission in EMP table can’t exceed 20% of average commission of the organization.

CREATE OR REPLACE TRIGGER disburse_comm_trg
AFTER INSERT OR UPDATE 
ON Emp 
FOR EACH ROW 
DECLARE 
    l_increment Emp.Comm%TYPE; 
BEGIN 
    SELECT AVG(Sal)*.20
    INTO l_increment
    FROM Emp;
    IF l_increment < :NEW.Comm THEN
        RAISE_APPLICATION_ERROR(-20333, 'Not allowed! Commission beyond allowed limit.');
    END IF;
END disburse_comm_trg;
/

Thing to note here is, above trigger is created on “EMP” table and is also doing a select from “EMP” within the trigger body. Because the trigger will be fired for any change (DML) operation, and reading the same table will result mutating error.

Let’s verify by executing the following block which will act as triggering event.

--View Data before Update
SELECT Ename, Sal, Comm FROM Emp WHERE Ename = 'ALLEN';

ENAME		  SAL	    COMM
---------- ---------- ----------
ALLEN		 1600	     300


--Update throws mutating trigger error
UPDATE Emp
SET Comm = 2000
WHERE Ename = 'ALLEN';

ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.DISBURSE_COMM_TRG", line 4

ORA-04088: error during execution of trigger 'SCOTT.DISBURSE_COMM_TRG'

Q28 : How to remediate mutating trigger error using Autonomous transaction?

Ans: You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.

Declaring the trigger created above as Autonomous.

CREATE OR REPLACE TRIGGER disburse_comm_trg
AFTER INSERT OR UPDATE 
ON Emp 
FOR EACH ROW 
DECLARE 
    PRAGMA AUTONOMOUS_TRANSACTION;
    l_increment Emp.Comm%TYPE; 
BEGIN 
    SELECT AVG(Sal)*.20
    INTO l_increment
    FROM Emp;
    IF l_increment < :NEW.Comm THEN
RAISE_APPLICATION_ERROR(-20333, 'Not allowed! Commission beyond allowed limit.');
    END IF;
END disburse_comm_trg;
/

Let’s verify by executing the following block which will act as triggering event.

--View Data before Update
SELECT Ename, Sal, Comm FROM Emp WHERE Ename = 'ALLEN';

ENAME             SAL       COMM
---------- ---------- ----------
ALLEN            1600        300

--Update throws error, violating given scenario
UPDATE Emp
SET Comm = 2000
WHERE Ename = 'ALLEN';

ORA-20333: Not allowed! Commission beyond allowed limit.
ORA-06512: at "SCOTT.DISBURSE_COMM_TRG", line 9
ORA-04088: error during execution of trigger 'SCOTT.DISBURSE_COMM_TRG'

–Remediate mutating trigger error


UPDATE Emp
SET Comm = 350
WHERE Ename = 'ALLEN';

1 row updated.

--Verifying data after successful update
SELECT Ename, Sal, Comm FROM Emp WHERE Ename = 'ALLEN';

ENAME             SAL       COMM
---------- ---------- ----------
ALLEN            1600        350

Q29 : What are other ways to remediate mutating trigger error?

Ans: Refer Avoiding Mutating triggers

Q30 : Can you create a trigger for BEFORE OR AFTER triggering event?

Ans: No, BEFORE and AFTER can not be used in the same trigger. A trigger can be either BEFORE trigger or AFTER trigger.

0 Comments

Inline Feedbacks

View all comments