Triggers & User-Defined Functions — Concept Check
Questions on Triggers & User-Defined Functions. BEFORE/AFTER INSERT/UPDATE/DELETE triggers enforce cross-table logic; deterministic scalar UDFs encapsulate reusable computation safe for use in queries.
Questions in this quiz
8 questions on Triggers & User-Defined Functions — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.A database application uses a BEFORE INSERT trigger to set default values. What happens if the trigger encounters an error?
- AThe trigger is skipped, but the row is inserted.
- BThe entire transaction is rolled back.
- COnly the trigger execution is aborted.
- DThe error is logged, but the row is inserted.
2.A developer wants to use a trigger to enforce a business rule across multiple tables. Which trigger type should they use?
- ABEFORE DELETE
- BBEFORE INSERT
- CAFTER UPDATE
- DAFTER INSERT
3.A team uses a UDF to calculate tax in SQL queries. They mark it as DETERMINISTIC. Why?
- ATo ensure the function runs faster.
- BTo enable result caching by the optimizer.
- CTo make the function available to all applications.
- DTo allow the function to modify data.
4.A developer is debugging a trigger that doesn't seem to execute. What might be a reason?
- AThe trigger is not committed yet.
- BThe trigger is disabled by default.
- CTriggers require manual execution.
- DThe trigger is not associated with any event.
5.Which of the following is a valid use case for an AFTER UPDATE trigger?
- ALogging changes to an audit table.
- BSetting default values before insertion.
- CRolling back transactions on error.
- DValidating input data before update.
6.Why might a developer prefer application-level logic over triggers in a microservices architecture?
- ATriggers are not supported in microservices.
- BApplication-level logic is easier to test and maintain.
- CTriggers cannot enforce cross-table logic.
- DApplication-level logic executes faster than triggers.
7.A trigger updates a denormalized table after an INSERT. What is a potential drawback?
- ATriggers cannot access OLD values.
- BTriggers cannot be used for audit logging.
- CTriggers can only modify the NEW row values.
- DTriggers are invisible to application code.
8.A UDF is used to calculate distances between coordinates. What should be considered when using it in queries?
- AIt cannot be used in WHERE clauses.
- BIt must be marked as MODIFIES SQL DATA.
- CIt can only be used in SELECT statements.
- DIt should be marked as DETERMINISTIC if inputs are consistent.