Stored Procedures & Triggers
Master the IT-English vocabulary of stored procedures and triggers: parameters, side effects, BEFORE/AFTER events and business logic.
0 / 29 completed
1 / 29
What is a 'stored procedure'?
A stored procedure is reusable SQL logic stored in the database and callable with parameters.
2 / 29
A trigger fires 'AFTER INSERT'. What does that mean?
An AFTER INSERT trigger runs automatically just after a row is inserted, e.g. to write an audit record.
3 / 29
A reviewer warns a trigger has a hidden 'side effect'. What does that mean?
A side effect is an implicit change (e.g. updating another table) that isn't obvious from the triggering statement.
4 / 29
A stored procedure takes an 'IN' parameter and an 'OUT' parameter. What is the OUT parameter for?
An OUT parameter passes a result back to the caller, as opposed to an IN parameter that supplies input.
5 / 29
Which sentence correctly uses 'business logic in the database'?
Encoding rules in procedures/triggers centralises logic in the DB, with trade-offs around testing and versioning.
6 / 29
Option A: The procedure is inefficient due to its lack of error handling. Option B: The procedure doesn't adhere to database normalization principles. Option C: The procedure lacks sufficient business logic to ensure data integrity and prevent potential financial discrepancies.
Option D: The procedure uses an outdated SQL dialect, causing compatibility issues.
Sarah's comment highlights the importance of *business logic* within a stored procedure—specifically, handling error conditions. A well-designed procedure should not just perform its primary task but also consider and manage potential failures to maintain data consistency. Returning the original amount in case of failure directly contradicts this principle and introduces a serious risk of incorrect transactions. Option A focuses on efficiency, which is related but doesn't address the core issue of error handling and financial integrity.
7 / 29
Mark's comment highlights a critical risk: data integrity. While automating salary payments sounds efficient, the trigger isn't validating crucial information like correct bank account formats or sufficient funds before transferring money. This is a fundamental business logic concern—the trigger should ensure that transactions are valid *before* making changes to sensitive financial records. Option A and D refer to technical performance and syntax issues respectively, which were not the core of Mark's concern; option C relates to security access rights, an entirely separate issue.
8 / 29
John, a junior developer, sent the following Slack message during a code review of a new e-commerce order processing stored procedure:
"Just ran this and it seems to work fine. The trigger automatically updates the shipping address in the database after each successful order placement. It's pretty concise."John's message highlights a critical issue: the trigger lacks validation of the shipping address. While concise code can be desirable, it shouldn't compromise data integrity. The trigger blindly updates the database without checking for valid formats or potential errors – this could lead to significant problems with delivery accuracy and customer satisfaction. This is a common oversight where developers prioritize brevity over thoroughness in triggers.
9 / 29
Option A: The procedure is inefficient due to its lack of error handling. Option B: The procedure doesn't adhere to database normalization principles. Option C: The procedure lacks sufficient business logic to ensure data integrity and prevent potential financial discrepancies.
Option D: The procedure uses an outdated SQL dialect, causing compatibility issues.
Sarah's comment highlights the importance of *business logic* within a stored procedure—specifically, handling error conditions. A well-designed procedure should not just perform its primary task but also consider and manage potential failures to maintain data consistency. Returning the original amount in case of failure directly contradicts this principle and introduces a serious risk of incorrect transactions. Option A focuses on efficiency, which is related but doesn't address the core issue of error handling and financial integrity.
10 / 29
Mark's comment highlights a critical risk: data integrity. While automating salary payments sounds efficient, the trigger isn't validating crucial information like correct bank account formats or sufficient funds before transferring money. This is a fundamental business logic concern—the trigger should ensure that transactions are valid *before* making changes to sensitive financial records. Option A and D refer to technical performance and syntax issues respectively, which were not the core of Mark's concern; option C relates to security access rights, an entirely separate issue.
11 / 29
John, a junior developer, sent the following Slack message during a code review of a new e-commerce order processing stored procedure:
"Just ran this and it seems to work fine. The trigger automatically updates the shipping address in the database after each successful order placement. It's pretty concise."John's message highlights a critical issue: the trigger lacks validation of the shipping address. While concise code can be desirable, it shouldn't compromise data integrity. The trigger blindly updates the database without checking for valid formats or potential errors – this could lead to significant problems with delivery accuracy and customer satisfaction. This is a common oversight where developers prioritize brevity over thoroughness in triggers.
12 / 29
Option A: The procedure is inefficient due to its lack of error handling. Option B: The procedure doesn't adhere to database normalization principles. Option C: The procedure lacks sufficient business logic to ensure data integrity and prevent potential financial discrepancies.
Option D: The procedure uses an outdated SQL dialect, causing compatibility issues.
Sarah's comment highlights the importance of *business logic* within a stored procedure—specifically, handling error conditions. A well-designed procedure should not just perform its primary task but also consider and manage potential failures to maintain data consistency. Returning the original amount in case of failure directly contradicts this principle and introduces a serious risk of incorrect transactions. Option A focuses on efficiency, which is related but doesn't address the core issue of error handling and financial integrity.
13 / 29
Mark's comment highlights a critical risk: data integrity. While automating salary payments sounds efficient, the trigger isn't validating crucial information like correct bank account formats or sufficient funds before transferring money. This is a fundamental business logic concern—the trigger should ensure that transactions are valid *before* making changes to sensitive financial records. Option A and D refer to technical performance and syntax issues respectively, which were not the core of Mark's concern; option C relates to security access rights, an entirely separate issue.
14 / 29
John, a junior developer, sent the following Slack message during a code review of a new e-commerce order processing stored procedure:
"Just ran this and it seems to work fine. The trigger automatically updates the shipping address in the database after each successful order placement. It's pretty concise."John's message highlights a critical issue: the trigger lacks validation of the shipping address. While concise code can be desirable, it shouldn't compromise data integrity. The trigger blindly updates the database without checking for valid formats or potential errors – this could lead to significant problems with delivery accuracy and customer satisfaction. This is a common oversight where developers prioritize brevity over thoroughness in triggers.
15 / 29
Option A: The procedure is inefficient due to its lack of error handling. Option B: The procedure doesn't adhere to database normalization principles. Option C: The procedure lacks sufficient business logic to ensure data integrity and prevent potential financial discrepancies.
Option D: The procedure uses an outdated SQL dialect, causing compatibility issues.
Sarah's comment highlights the importance of *business logic* within a stored procedure—specifically, handling error conditions. A well-designed procedure should not just perform its primary task but also consider and manage potential failures to maintain data consistency. Returning the original amount in case of failure directly contradicts this principle and introduces a serious risk of incorrect transactions. Option A focuses on efficiency, which is related but doesn't address the core issue of error handling and financial integrity.
16 / 29
Mark's comment highlights a critical risk: data integrity. While automating salary payments sounds efficient, the trigger isn't validating crucial information like correct bank account formats or sufficient funds before transferring money. This is a fundamental business logic concern—the trigger should ensure that transactions are valid *before* making changes to sensitive financial records. Option A and D refer to technical performance and syntax issues respectively, which were not the core of Mark's concern; option C relates to security access rights, an entirely separate issue.
17 / 29
John, a junior developer, sent the following Slack message during a code review of a new e-commerce order processing stored procedure:
"Just ran this and it seems to work fine. The trigger automatically updates the shipping address in the database after each successful order placement. It's pretty concise."John's message highlights a critical issue: the trigger lacks validation of the shipping address. While concise code can be desirable, it shouldn't compromise data integrity. The trigger blindly updates the database without checking for valid formats or potential errors – this could lead to significant problems with delivery accuracy and customer satisfaction. This is a common oversight where developers prioritize brevity over thoroughness in triggers.
18 / 29
During a standup meeting, David explains the new `update_user_profile` stored procedure. He says: 'This trigger automatically updates the user's email address whenever they change it in the application.' What is David primarily describing?
David is explaining a trigger – specifically, an event-driven behavior where changes in the application automatically propagate to the database via a trigger. Triggers are designed to react to data modifications, not for ETL or scheduled jobs. Data validation rules are typically handled through constraints and checks within the stored procedure itself.
19 / 29
Maria is reviewing a PR for a new e-commerce order processing stored procedure. The PR includes the following comment: 'The trigger automatically updates the inventory levels based on each successful order.' What does Maria likely mean by 'automatically' in this context?
'Automatically' refers to the trigger's reactive nature – it executes without needing a user or another process to start it. The trigger responds directly to changes in the order data, ensuring inventory is updated immediately. Options A, C and D are all incorrect descriptions of how triggers function.
20 / 29
You're discussing a complex stored procedure with your team. One developer suggests using a trigger to ensure data integrity for a critical financial transaction. Which of the following best describes the primary purpose of using a trigger in this scenario?
Triggers are primarily about enforcing business rules – they react to database events (like an INSERT or UPDATE) and automatically execute predefined actions to maintain data integrity. While triggers *can* indirectly affect performance, their core purpose isn't optimization. Simplification and centralized access management are not the trigger's functions.
21 / 29
Sarah is reviewing a new stored procedure for managing customer refunds. The PR description states: 'This trigger automatically updates the refund status and applies any associated discounts after a refund request is submitted.' What potential risk does Sarah need to investigate regarding this trigger?
Sarah needs to assess the risk of race conditions – concurrent execution of the trigger could lead to inconsistent refund statuses if not handled correctly with proper locking or transaction management. The other options are less critical concerns for initial review.
22 / 29
During a code review of a new inventory management stored procedure, Emily (the QA engineer) posts this comment to the PR: 'The trigger automatically updates the product's stock level whenever an order is placed. However, it doesn't consider potential backorders or partial shipments – which could lead to inaccurate reporting.' What is Emily primarily raising concern about regarding the trigger's functionality?
Emily is highlighting a critical flaw in the trigger's design: its inability to account for realistic order complexities like backorders and partial shipments. This omission could lead to misleading inventory reports. The other options – database credentials, API versions, and error logging – are separate concerns not directly addressed by her comment; she's focusing on the core logic of the trigger itself.
23 / 29
In a Slack channel discussing a new user onboarding stored procedure, Alex (a senior developer) writes: 'I've implemented this trigger to automatically assign users to relevant support teams based on their initial signup location. It's crucial for routing them to the correct specialist quickly.' What is Alex emphasizing about the purpose of the trigger?
Alex clearly states that the trigger's core function is to route users to support teams based on their signup location – a direct and important use case for triggers in this scenario. While other options may be related tasks (email sending, reporting), they aren't the primary purpose Alex describes.
24 / 29
During a standup meeting, Ben (a developer) explains the new `process_loan_application` stored procedure. He says: 'We've added a trigger that automatically updates the loan application status to 'Approved' when the credit score exceeds 700 and 'Rejected' otherwise.' What does Ben primarily describe?
Ben is detailing a trigger that acts as a conditional logic block *within* the stored procedure – it automatically updates the status based on the credit score. This directly reflects how triggers are used for automated actions triggered by database events, rather than a full approval/rejection system.
25 / 29
During a code review of a new e-commerce product catalog stored procedure, Lisa (the database administrator) flags this change in the PR description: 'This trigger automatically updates the product's availability status after each order placement.' Which statement best describes Lisa's concern?
trigger update_product_availability after for each order;
Lisa's concern is that the trigger lacks consideration for complex scenarios like backorders or pre-orders. A robust trigger needs to account for potential changes in inventory levels that aren't simply triggered by a new order. The other options represent misunderstandings of trigger functionality – they incorrectly suggest triggers should handle error conditions or be overly simplistic.
26 / 29
In a Slack channel discussing the implementation of a new `user_login` stored procedure, David (a junior developer) posts: 'I've added this trigger to automatically update the user's last login timestamp whenever they successfully log in. It improves our reporting.' What is the primary benefit David highlights?
trigger update_last_login on user_login after for each user;David correctly identifies that the trigger's primary benefit is simplifying reporting. The timestamp updates provide a readily available record of user activity for various analytical purposes. The other options misrepresent the purpose—data integrity isn't directly ensured by this type of trigger, security is a broader concern handled elsewhere, and server load reduction isn't its function.
27 / 29
During a standup meeting, Sarah explains the new `process_payment` stored procedure. She states: 'This trigger automatically updates the payment status to 'Completed' when the payment is successfully processed.' What does this trigger primarily achieve?
trigger update_payment_status on process_payment after for each payment;Sarah's statement accurately describes the trigger's function: ensuring accurate transaction tracking. The 'Completed' status update provides a clear record of payment success for reporting and reconciliation purposes. The other options represent potential additional functionalities (duplicate prevention, refund handling) which aren't inherent to this simple trigger.
28 / 29
Maria is reviewing a PR for a new customer support ticket stored procedure. The PR includes the following comment: 'This trigger automatically updates the ticket's status to 'Resolved' when an agent manually closes it.' What potential problem does this trigger introduce?
trigger update_ticket_status on close_ticket after for each ticket;The core issue is the dependency Maria identifies – the trigger forces a status change based on agent action. This can lead to inconsistencies and potential problems if the agent doesn't correctly update the status or if there's an error in the system. The other options represent desirable outcomes but aren't directly related to this specific trigger design.
29 / 29
You are reviewing a PR for a new employee expense report stored procedure and see the following comment: 'This trigger automatically updates the expense report's status to 'Approved' when it exceeds a specific monetary threshold.' What is the primary advantage of this approach?
trigger update_expense_report_status on submit_expense after for each report where amount > 1000;The primary advantage is that it automates the approval workflow for reports exceeding the threshold. This reduces manual effort and speeds up the processing time significantly. The other options describe beneficial outcomes but aren't the direct reason for this specific trigger's implementation – accuracy and reporting are general benefits of well-designed procedures.
Frequently Asked Questions
What does the "Stored Procedures & Triggers" exercise practise?
Master the IT-English vocabulary of stored procedures and triggers: parameters, side effects, BEFORE/AFTER events and business logic.
How many questions are in this exercise?
This exercise has 29 questions, each multiple-choice with a full explanation shown after you answer.
What English level is this exercise for?
This exercise is tagged Intermediate. If the vocabulary feels difficult, browse the Database & SQL category page for an easier module to start with.
Is this exercise free to use?
Yes. Every exercise on CoderSlingo, including this one, is free with no account, sign-up, or paywall.
Do I get feedback if I answer incorrectly?
Yes — whichever option you choose, right or wrong, you'll immediately see an explanation clarifying the correct term and why the other options don't fit.
Can I retry this exercise?
Yes — once you finish all the questions, a "Try again" button on the results screen resets the exercise so you can practise as many times as you like.
Do I need an account to track my progress?
No account is required. Your progress bar and score for this session are tracked in the browser as you go, but nothing is saved once you leave the page.
Is "Stored Procedures & Triggers" part of a larger series?
Yes — it's one exercise in the Database & SQL category on CoderSlingo. See the category page for the full list of related exercises on similar terminology.
Can I link directly to this exercise?
Yes — this exercise has its own permanent URL, so you can bookmark it or share the link directly with a colleague or study partner.
Where can I find more exercises like this one?
See the Database & SQL category page for related exercises, or browse the main Exercises hub for other IT English topics.