Interview Corner

A: A stored procedure is explicitly called using the CALL statement — you control when and how it runs. A trigger fires automatically in response to a DML event (INSERT, UPDATE, DELETE) on a table — you do not call it directly. Procedures can have IN/OUT parameters and return result sets. Triggers have no parameters but can access OLD and NEW row values. Procedures are used for complex business workflows. Triggers are used for audit logging, validation, and maintaining derived data. From a testing perspective, procedures are tested by calling them and checking outputs and side effects, while triggers are tested by performing the triggering action and verifying the side effects.

A: I would create a structured test plan with these categories: (1) Happy path — transfer between two valid active accounts with sufficient balance, verify both balances changed correctly, and check the transaction log entry. (2) Insufficient funds — try transferring more than the available balance, verify both balances are unchanged. (3) Invalid accounts — non-existent source, non-existent destination, frozen or closed accounts. (4) Boundary values — transfer exact balance (zero remaining), transfer Rs 0.01, transfer maximum allowed amount. (5) Negative/zero amount — should be rejected. (6) Self-transfer — same source and destination. (7) Concurrent access — two transfers from the same account simultaneously to check for race conditions. (8) Transaction integrity — if the procedure fails mid-way, verify that no partial changes remain (ROLLBACK worked correctly).

A: SQLSTATE 45000 is a user-defined exception code in MySQL. It is used to raise custom business rule errors from stored procedures and triggers using the SIGNAL statement. For example: SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT = "Insufficient funds". When this is raised, the current operation is aborted, and the calling application receives an SQLException with error code 1644 and the custom message. As a QA tester, I verify that the correct SQLSTATE and MESSAGE_TEXT are raised for each business rule violation. I also check that the procedure properly rolls back any partial changes before raising the signal.

A: Several risks: (1) Invisible side effects — triggers fire automatically, so developers and testers might not know they exist, leading to unexpected data changes. (2) Trigger chains — trigger on table A modifies table B, which has its own trigger modifying table C, creating a cascading domino effect that is hard to debug. (3) Performance — triggers add overhead to every INSERT/UPDATE/DELETE, even simple ones. A slow trigger slows down every operation on that table. (4) Maintenance — when business rules change, someone must remember to update the trigger. Forgotten triggers enforce outdated rules. (5) Deadlocks — triggers that lock rows in other tables can cause deadlocks under concurrent load. As a QA, I always ask: what triggers exist on this table? Then I verify their behavior and check for unintended chain effects.

A: I test error handling in three areas: (1) DECLARE HANDLER — I deliberately cause the specific error (like inserting a duplicate key) and verify the handler catches it, sets the correct result message, and does not leave partial data. (2) SIGNAL SQLSTATE — I provide inputs that violate business rules and verify the correct error code (1644) and message text are returned. I also check that the database state is unchanged after the error. (3) Transaction ROLLBACK — I test scenarios where the procedure fails mid-transaction and verify that all changes from that transaction are undone. The most dangerous bug I look for is a CONTINUE HANDLER that swallows errors silently — the procedure returns SUCCESS but the data is incomplete.

A: Yes. Selenium tests often need to set up or verify database state. I use JDBC with CallableStatement from the test setup (@BeforeMethod) or verification steps. The syntax is: CallableStatement stmt = conn.prepareCall("{CALL procedure_name(?, ?)}"). I set IN parameters with setString/setInt, register OUT parameters with registerOutParameter, call execute(), and read results with getString/getInt. For test isolation, I use setAutoCommit(false) and rollback() in teardown so each test starts with clean data. This approach is common in integration tests where we need to verify that the UI action correctly triggered the stored procedure and the database state is accurate.

A: EXIT HANDLER catches the error, executes its handler body, and immediately exits the current BEGIN...END block — execution does not continue after the failed statement. CONTINUE HANDLER catches the error, executes its handler body, and then continues with the next statement after the one that failed. EXIT HANDLER is safer for critical operations because it prevents the procedure from continuing with potentially corrupted state. CONTINUE HANDLER is used when you want to skip over non-critical errors — for example, when processing rows in a loop and you want to log errors but keep processing the remaining rows. As a QA, I verify that EXIT HANDLER is used for critical paths and that CONTINUE HANDLER does not accidentally hide important errors.