programming
beginner
10 sample questions
Stored Procedures & Triggers MCQ Practice Test
Predefined SQL code (procedures) and automatic actions (triggers) executed on events.
Q1. A stored procedure and a trigger are both database objects used to perform specific tasks. What is the primary difference in how they are executed?
-
A. A stored procedure is executed automatically by the database, while a trigger is called explicitly.
-
B. A stored procedure is designed to enforce data security, while a trigger is designed for data integrity.
-
C. A stored procedure is called explicitly by a user or application, while a trigger is automatically executed in response to a database event. ✓
-
D. A stored procedure can only be used with SELECT statements, while a trigger can be used with any DML statement.
Explanation: Stored procedures are executed explicitly by calling them, whereas triggers are automatically activated by database events (e.g., INSERT, UPDATE, DELETE operations).
Q2. A stored procedure is created to update a customer's address. The procedure checks if the customer's ID exists in the database. If it does, it updates the address. If not, it inserts a new record with the customer's ID and the updated address. Which of the following statements best describes the purpose of a trigger in this scenario?
-
A. to prevent duplicate customer records
-
B. to enforce data consistency by updating dependent tables ✓
-
C. to notify the database administrator of the update operation
-
D. to validate user input before executing the stored procedure
Explanation: A trigger is a database object that automatically executes a set of actions in response to certain events, such as insert, update, or delete operations. In this scenario, a trigger could be used to update dependent tables, such as the customer's order history, when the customer's address is updated.
Q3. Which trigger timing lets you validate or block an operation before it happens?
-
A. A BEFORE trigger, which runs before the insert or update is applied ✓
-
B. An AFTER trigger
-
C. A statement-level trigger only
-
D. No trigger can do this
Explanation: BEFORE triggers run prior to the change, so they can validate input or modify the new values; the original options mixed timing and granularity confusingly.
Q4. Which of the following is a characteristic of a stored procedure in a database?
-
A. It is a program that runs on the client-side.
-
B. It is a set of SQL statements that can be executed multiple times without recompilation. ✓
-
C. It is a trigger that can be used to perform actions before or after data modification.
-
D. It is a view that allows multiple queries to be executed simultaneously.
Explanation: A stored procedure is a set of SQL statements that can be executed multiple times without recompilation, making it more efficient and improving database performance.
Q5. A stored procedure is created to update the salary of employees in a table. Which of the following statements is true about the stored procedure?
-
A. It can only be executed from within the database.
-
B. It can be executed from any application that has a connection to the database. ✓
-
C. It can only be created by a database administrator.
-
D. It can only be used to select data.
Explanation: A stored procedure is a set of SQL statements that can be executed multiple times from an application, and it can be created by any user with the necessary permissions. It can be executed from any application that has a connection to the database, making it a reusable piece of code.
Q6. A database contains a table with a column named "salary" and another column named "bonus". A trigger is created to update the "salary" column whenever the "bonus" column is updated. The trigger is written in SQL and uses the "UPDATE" statement. Which of the following statements about the trigger is correct?
-
A. The trigger will only fire when the "bonus" column is updated directly, not when it is updated through a view.
-
B. The trigger will only fire when the "bonus" column is updated through a stored procedure.
-
C. The trigger will fire when the "bonus" column is updated through any means, including direct updates, views, or stored procedures. ✓
-
D. The trigger will only fire when the "salary" column is updated directly.
Explanation: A trigger in SQL will fire when the specified action occurs, regardless of the method used to cause the action. In this case, the trigger is set up to fire when the "bonus" column is updated, which includes direct updates, updates through views, or updates through stored procedures.
Q7. A stored procedure is created to update a table with a new salary for employees. The procedure uses a trigger to insert a new record into a separate audit table. What is the purpose of the trigger in this scenario?
-
A. To enforce data integrity by preventing incorrect updates to the salary table.
-
B. To maintain an audit trail of all changes made to the salary table. ✓
-
C. To optimize the performance of the stored procedure by reducing the number of queries executed.
-
D. To handle concurrency issues by locking the salary table during updates.
Explanation: The trigger is used to insert a new record into the audit table, which allows for tracking of all changes made to the salary table. This is an example of an auditing mechanism, which is a common use case for triggers.
Q8. A stored procedure is called from a trigger to perform an action on a table. What is the primary reason for using a transaction in this scenario?
-
A. To ensure data consistency across multiple rows ✓
-
B. To improve performance by reducing the number of database calls
-
C. To handle concurrency issues when multiple transactions are executing simultaneously
-
D. To enable auditing and logging of database activities
Explanation: When a stored procedure is called from a trigger, it's essential to use a transaction to ensure data consistency. This is because the trigger may modify multiple rows, and a transaction allows you to roll back changes in case of an error or inconsistency.
Q9. A stored procedure is called from a trigger to perform an action. Which of the following statements is true about the execution of the stored procedure?
-
A. The stored procedure will be executed once for each row affected by the trigger. ✓
-
B. The stored procedure will be executed only once for the entire trigger.
-
C. The stored procedure will be executed for each row affected by the trigger, but only if the trigger is enabled.
-
D. The stored procedure will be executed only once for the entire trigger, and only if the trigger is enabled.
Explanation: A stored procedure called from a trigger will be executed once for each row affected by the trigger. This is because triggers are typically row-level triggers, and they fire once for each row that meets the trigger condition.
Q10. In a database, what is the primary purpose of a trigger in relation to a stored procedure that updates a table?
-
A. To prevent the stored procedure from modifying the table.
-
B. To enforce data integrity by validating changes made by the stored procedure. ✓
-
C. To notify other database components of the changes made by the stored procedure.
-
D. To log the changes made by the stored procedure to an audit table.
Explanation: A trigger in a database is primarily used to enforce data integrity by validating changes made by a stored procedure or other database operations. It can also be used to perform additional actions before or after the operation, such as logging changes to an audit table. However, its primary purpose is to ensure that the data being modified conforms to the defined rules and constraints.
That was just a sample. Sign up to unlock the full question bank with timed tests and certificates.
Sign Up Free