Oracle Database Programming MCQs with Answers
Practice important Oracle Database Programming MCQs with answers and explanations.
Multiple Choice Questions
Q291: Which type of trigger is executed before the triggering statement?
- A: AFTER trigger
- B: INSTEAD OF trigger
- C: BEFORE trigger
- D: ROW trigger
View Answer
C
Q292: What is the primary difference between a function and a procedure in PL/SQL?
- A: A function does not return a value, but a procedure does
- B: A function must return a value, but a procedure may or may not return a value
- C: A function can only be used in SQL statements
- D: A procedure cannot have parameters
View Answer
B
Q293: What type of trigger would you use to log events in a database table?
- A: BEFORE trigger
- B: AFTER trigger
- C: DML trigger
- D: DDL trigger
View Answer
C
Q294: Which of the following is the correct syntax to create a trigger in PL/SQL?
- A: CREATE TRIGGER trigger_name ON table_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}
- B: CREATE [OR REPLACE] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE} ON table_name
- C: CREATE TRIGGER trigger_name {INSERT | UPDATE | DELETE} ON table_name [FOR EACH ROW]
- D: CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} ON table_name {INSERT | UPDATE | DELETE}
View Answer
B
Q295: A trigger created on a view will be fired when a DML statement is executed on the associated:
- A: Table
- B: Schema
- C: Database
- D: Trigger
View Answer
D
Q296: What does the `WHEN` clause in a trigger do?
- A: It specifies the trigger action
- B: It determines the trigger timing
- C: It defines the condition under which the trigger is executed
- D: It disables the trigger
View Answer
B
Q297: In the following syntax, which keyword specifies that the trigger should be fired before an UPDATE statement?
- A: BEFORE
- B: AFTER
- C: INSTEAD OF
- D: WHEN
View Answer
A
Q298: What type of trigger is used to enforce referential integrity constraints in a table?
- A: Row-level trigger
- B: Statement-level trigger
- C: BEFORE trigger
- D: AFTER trigger
View Answer
C
Q299: The following PL/SQL block is used to create a trigger that stores old product prices:
- A: CREATE OR REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price); END; /
- B: CREATE OR REPLACE TRIGGER price_history_trigger AFTER UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:old.product_id, :old.product_name, :old.supplier_name, :old.unit_price); END; /
- C: CREATE OR REPLACE TRIGGER price_history_trigger BEFORE UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:new.product_id, :new.product_name, :new.supplier_name, :new.unit_price); END; /
- D: CREATE OR REPLACE TRIGGER price_history_trigger AFTER UPDATE OF unit_price ON product FOR EACH ROW BEGIN INSERT INTO product_price_history VALUES(:new.product_id, :new.product_name, :new.supplier_name, :new.unit_price); END; /
View Answer
B
Q300: Which PL/SQL block creates a trigger to prevent insertion if the department number does not exist in the `dept` table?
- A: CREATE OR REPLACE TRIGGER Emp_dept_check BEFORE INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NOT NULL) DECLARE Dummy INTEGER; BEGIN SELECT Deptno FROM Dept WHERE Deptno = :new.Deptno; END;
- B: CREATE OR REPLACE TRIGGER Emp_dept_check BEFORE INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NOT NULL) DECLARE Dummy INTEGER; CURSOR Dummy_cursor IS SELECT Deptno FROM Dept WHERE Deptno = :new.Deptno FOR UPDATE OF Deptno; BEGIN OPEN
- C: CREATE OR REPLACE TRIGGER Emp_dept_check AFTER INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NOT NULL) BEGIN IF NOT EXISTS (SELECT 1 FROM Dept WHERE Deptno = :new.Deptno) THEN RAISE_APPLICATION_ERROR(-20000, 'Invalid Department Number
- D: CREATE OR REPLACE TRIGGER Emp_dept_check AFTER INSERT OR UPDATE OF Deptno ON Emp FOR EACH ROW WHEN (new.Deptno IS NULL) BEGIN RAISE_APPLICATION_ERROR(-20000, 'Deptno cannot be NULL'); END;
View Answer
B