MCQs on Triggers and Advanced PL/SQL | Automated Actions
Topic 1: Understanding and Creating Triggers
- What is a trigger in SQL?
- A) A stored procedure that is manually invoked
- B) A mechanism to enforce business rules automatically
- C) A function used to modify data
- D) A type of database index
- Which of the following statements is used to create a trigger in SQL?
- A) CREATE PROCEDURE
- B) CREATE FUNCTION
- C) CREATE TRIGGER
- D) CREATE VIEW
- What is the purpose of a trigger in a database?
- A) To monitor and control data changes
- B) To create views and indexes
- C) To retrieve and store data
- D) To provide security
- Which SQL statement is used to remove an existing trigger?
- A) REMOVE TRIGGER
- B) DROP TRIGGER
- C) DELETE TRIGGER
- D) DROP PROCEDURE
- A trigger can be fired when:
- A) A user logs into the database
- B) A table is created
- C) A DML operation is performed on a table
- D) A session ends
- A trigger can be associated with:
- A) Only INSERT operations
- B) Only UPDATE operations
- C) Only DELETE operations
- D) INSERT, UPDATE, DELETE operations
- Which of the following trigger types is executed automatically when an event occurs on a table?
- A) Stored Procedure
- B) Trigger
- C) View
- D) Index
- What is the main advantage of using triggers in a database?
- A) Enforcing data integrity automatically
- B) To store large amounts of data
- C) To simplify complex queries
- D) To back up the database automatically
- Which trigger event can be used to enforce business rules when data is updated?
- A) AFTER INSERT
- B) BEFORE UPDATE
- C) AFTER DELETE
- D) ON COMMIT
- Which of the following is a common use of triggers in a database?
- A) Managing user permissions
- B) Automatically updating related tables or logging changes
- C) Optimizing queries
- D) Enabling full-text search
Topic 2: Row-Level vs. Statement-Level Triggers
- Row-level triggers are executed:
- A) Once per statement
- B) Once per row affected by the statement
- C) Only when an error occurs
- D) Only when a record is inserted
- Which type of trigger is executed once for each row affected by a DML operation?
- A) Statement-level trigger
- B) Row-level trigger
- C) Both statement-level and row-level trigger
- D) Only a stored procedure
- Which of the following is true about statement-level triggers?
- A) They are fired once for each row in a DML operation
- B) They are fired once for the entire DML statement, regardless of the number of rows
- C) They cannot be used with DELETE statements
- D) They can only be used with INSERT statements
- Row-level triggers are useful for:
- A) Performing operations on each individual row affected
- B) Enforcing column constraints
- C) Generating automatic reports
- D) Updating all rows in a table
- Which type of trigger can be fired after a DML operation on a single row?
- A) Statement-level trigger
- B) Row-level trigger
- C) Both statement-level and row-level trigger
- D) None of the above
- A statement-level trigger is most appropriate for:
- A) Actions that need to be performed for every row modified
- B) Performing a task once per statement execution
- C) Generating logs for each row
- D) Filtering input data
- Which type of trigger should you use if you want to keep track of changes to each row in a table?
- A) Row-level trigger
- B) Statement-level trigger
- C) Both statement-level and row-level trigger
- D) Compound trigger
- In which scenario would a statement-level trigger be ideal?
- A) When you want to perform an action on a per-row basis
- B) When you need to execute an action only once for a multi-row update
- C) When you need to validate the data in every row
- D) When you need to delete rows from multiple tables
- Which of the following can be a disadvantage of row-level triggers?
- A) They execute for each row in a DML operation, which can impact performance
- B) They cannot access table values
- C) They are only supported in Oracle databases
- D) They cannot be used with UPDATE operations
- Statement-level triggers can be used to:
- A) Fire for each affected row
- B) Fire once for each transaction, regardless of the number of rows
- C) Only track INSERT statements
- D) Generate automatic audit logs
Topic 3: Before and After Triggers
- What does a “BEFORE” trigger do in SQL?
- A) It fires before a DML operation (INSERT, UPDATE, DELETE) is executed
- B) It fires after a DML operation is executed
- C) It fires before the database starts
- D) It runs after a stored procedure is invoked
- The main difference between a “BEFORE” trigger and an “AFTER” trigger is:
- A) A “BEFORE” trigger is executed before the operation, while an “AFTER” trigger is executed after
- B) A “BEFORE” trigger is executed after the operation, while an “AFTER” trigger is executed before
- C) They both execute after the operation
- D) They both execute before the operation
- Which of the following is true for “AFTER” triggers?
- A) They can be used to roll back an operation
- B) They execute before a DML operation
- C) They execute after the operation has completed
- D) They cannot be used for INSERT statements
- Which trigger type is ideal for validating data before it is committed to the database?
- A) AFTER trigger
- B) BEFORE trigger
- C) Compound trigger
- D) Statement-level trigger
- A “BEFORE” trigger is used to:
- A) Modify data before it is inserted or updated
- B) Log changes after a DML operation
- C) Roll back a transaction
- D) Lock the table during updates
- In an “AFTER” trigger, you can:
- A) Modify the data being inserted or updated
- B) Access data modified by the DML operation
- C) Prevent the operation from happening
- D) None of the above
- Which of the following triggers can be used to enforce business logic before an update is made?
- A) BEFORE INSERT
- B) AFTER UPDATE
- C) BEFORE UPDATE
- D) AFTER DELETE
- If you want to log all changes made to a table, which type of trigger would you use?
- A) BEFORE trigger
- B) AFTER trigger
- C) Compound trigger
- D) Row-level trigger
- Which of the following is a valid use case for an “AFTER” trigger?
- A) Validating user input before it is committed to the database
- B) Auditing changes after an operation is performed
- C) Changing data before it is inserted into a table
- D) Preventing unauthorized DML operations
- In a transaction, which trigger would execute first?
- A) AFTER trigger
- B) BEFORE trigger
- C) Compound trigger
- D) Both execute at the same time
Answers
| Qno | Answer |
|---|---|
| 1 | B) A mechanism to enforce business rules automatically |
| 2 | C) CREATE TRIGGER |
| 3 | A) To monitor and control data changes |
| 4 | B) DROP TRIGGER |
| 5 | C) A DML operation is performed on a table |
| 6 | D) INSERT, UPDATE, DELETE operations |
| 7 | B) Trigger |
| 8 | A) Enforcing data integrity automatically |
| 9 | B) BEFORE UPDATE |
| 10 | B) Automatically updating related tables or logging changes |
| 11 | B) Once per row affected by the statement |
| 12 | B) Row-level trigger |
| 13 | B) They are fired once for the entire DML statement, regardless of the number of rows |
| 14 | A) Performing operations on each individual row affected |
| 15 | B) Row-level trigger |
| 16 | B) Performing a task once per statement execution |
| 17 | A) Row-level trigger |
| 18 | B) When you need to execute an action only once for a multi-row update |
| 19 | A) They execute for each row in a DML operation, which can impact performance |
| 20 | B) Fire once for each transaction, regardless of the number of rows |
| 21 | A) It fires before a DML operation (INSERT, UPDATE, DELETE) is executed |
| 22 | A) A “BEFORE” trigger is executed before the operation, while an “AFTER” trigger is executed after |
| 23 | C) They execute after the operation has completed |
| 24 | B) BEFORE trigger |
| 25 | A) Modify data before it is inserted or updated |
| 26 | B) Access data modified by the DML operation |
| 27 | C) BEFORE UPDATE |
| 28 | B) AFTER trigger |
| 29 | B) Auditing changes after an operation is performed |
| 30 | B) BEFORE trigger |