Stored Procedures & Triggers Flashcards
Functions vs Procedures (Oracle PL/SQL, Postgres PL/pgSQL) Trigger types: BEFORE, AFTER, INSTEAD OF Writing procedures with control flow Exception handling in procedures Invoking from client code or SQL Security and performance considerations (30 cards)
What is the difference between a function and a procedure in PL/SQL or PL/pgSQL?
Functions return a value and can be used in SQL statements, while procedures perform actions without returning a value and are invoked independently.
What are BEFORE triggers?
BEFORE triggers execute prior to an insert, update, or delete operation, often used for validation or transformation.
What are AFTER triggers?
AFTER triggers execute after the triggering statement, useful for logging, auditing, or chained operations.
What is an INSTEAD OF trigger?
INSTEAD OF triggers override the action on a view, enabling updates or inserts on views that normally can’t be modified.
How do you write control flow in procedures?
Control flow in procedures uses constructs like IF, CASE, LOOP, WHILE, and FOR to manage logic.
How is exception handling done in stored procedures?
Use BEGIN…EXCEPTION…END blocks in PL/pgSQL or EXCEPTION WHEN in PL/SQL to catch and handle errors.
How can procedures be invoked from SQL or client code?
Procedures can be called using CALL in SQL or invoked using database drivers from client applications.
What are security considerations for stored procedures?
They can encapsulate permissions, reducing direct access to tables, but care is needed with SQL injection risks.
What are performance advantages of stored procedures?
They reduce network traffic, enable logic reuse, and may benefit from pre-compilation by the database engine.
What are disadvantages of stored procedures?
They can lead to business logic duplication, be harder to version control, and create vendor lock-in.
What is a best practice for using triggers?
Use triggers sparingly for side-effects like auditing, and avoid complex logic to prevent performance bottlenecks.
When should functions be preferred over procedures?
When a computation is needed in a SELECT statement or to return a single value, use functions.
What’s a good use case for an INSTEAD OF trigger?
To allow inserts or updates on a view that combines multiple tables or filters out rows.
What’s the impact of stored procedures on system design?
They centralize business logic in the DB layer, reducing reliance on app logic but making debugging more complex.
How can stored procedures affect architectural decisions?
They may require teams to maintain more logic at the DB level, possibly affecting deployment strategies.
How do stored procedures improve fault tolerance?
They can enforce consistent logic and exception handling, reducing application-level inconsistencies.
What’s a way to debug stored procedures?
Use RAISE NOTICE or DBMS_OUTPUT.PUT_LINE for debugging, or enable detailed error logging.
What’s a performance tradeoff with triggers?
They add hidden execution cost and can cause cascading logic that’s hard to trace or optimize.
What is a real-world tradeoff of using stored procedures?
They simplify client code but make the database layer heavier and harder to maintain with source control.
What’s a common interview question about triggers?
Explain the difference between BEFORE and AFTER triggers, and provide a use case for each.
What is a gotcha with AFTER triggers in PostgreSQL?
They do not fire if a BEFORE trigger cancels the operation, and their execution order isn’t guaranteed.
What is a potential issue with using triggers extensively?
They can lead to non-transparent logic flow and unpredictable side effects if overused.
What’s a best practice for exception handling in procedures?
Log meaningful error messages, avoid silent failures, and re-throw if needed for higher-level handling.
What’s a limitation of using stored functions in queries?
They can’t modify data and may have limitations on transaction control depending on the DBMS.
