AFTER trigger execution in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after an AFTER INSERT trigger?

Consider a table Orders with columns OrderID and Status. An AFTER INSERT trigger updates the Status to 'Processed' after a new order is inserted.

What will be the Status of the newly inserted order immediately after the insert completes?

SQL

CREATE TABLE Orders (OrderID INT PRIMARY KEY, Status VARCHAR(20));

CREATE TRIGGER trg_AfterInsert
AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
  UPDATE Orders SET Status = 'Processed' WHERE OrderID = NEW.OrderID;
END;

INSERT INTO Orders (OrderID, Status) VALUES (1, 'Pending');

SELECT Status FROM Orders WHERE OrderID = 1;

A 'Processed'

B 'Pending'

C NULL

D Trigger causes error, no output

💡 Hint

Think about when AFTER triggers run in relation to the insert operation.

AFTER INSERT triggers run after the row is inserted. The trigger updates the Status to 'Processed', so the final value is 'Processed'.

🧠 Conceptual

intermediate

When does an AFTER UPDATE trigger execute?

Choose the correct statement about when an AFTER UPDATE trigger runs in a database.

A After the update operation modifies the row

B Before the update operation modifies the row

C Only if the update operation fails

D Before the update operation starts scanning rows

💡 Hint

AFTER triggers run after the triggering event completes.

AFTER UPDATE triggers execute after the row has been updated successfully.

🔧 Debug

advanced

Why does this AFTER DELETE trigger not delete related rows?

Given the following trigger, why does it fail to delete related rows in OrderDetails after deleting from Orders?

SQL

CREATE TRIGGER trg_AfterDelete
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
  DELETE FROM OrderDetails WHERE OrderID = OLD.OrderID;
END;

DELETE FROM Orders WHERE OrderID = 10;

A The trigger should be BEFORE DELETE to access OLD values

B The trigger syntax is correct and will delete related rows

C The DELETE statement inside the trigger causes a recursion error

D The trigger uses OLD but AFTER triggers cannot access OLD values

💡 Hint

Consider if AFTER DELETE triggers can access OLD values and perform deletes.

AFTER DELETE triggers can access OLD values and perform actions like deleting related rows. The syntax is correct and the trigger will delete related rows.

📝 Syntax

advanced

Identify the syntax error in this AFTER UPDATE trigger

Which option correctly fixes the syntax error in this AFTER UPDATE trigger?

SQL

CREATE TRIGGER trg_AfterUpdate
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
  IF NEW.Salary > OLD.Salary THEN
    UPDATE Employees SET Status = 'Promoted' WHERE EmployeeID = NEW.EmployeeID;
  END IF;
END;

A Change AFTER UPDATE to BEFORE UPDATE

B Remove END IF; because it's not needed in triggers

C Replace BEGIN with BEGIN TRANSACTION

D Add THEN after IF condition: IF NEW.Salary > OLD.Salary THEN

💡 Hint

Check the IF statement syntax in SQL triggers.

In SQL triggers, IF statements require THEN after the condition. Missing THEN causes syntax error.

❓ optimization

expert

Optimizing AFTER INSERT trigger to avoid unnecessary updates

An AFTER INSERT trigger updates a column Status to 'Active' for every new row. How can you optimize the trigger to avoid updating rows that already have Status = 'Active'?

A Remove the trigger and update Status manually after inserts

B Change the trigger to BEFORE INSERT and set Status directly

C Add a WHERE clause to update only rows where Status is not 'Active'

D Use a DELETE statement inside the trigger to remove rows with Status 'Active'

💡 Hint

Think about how to limit updates to only necessary rows.

Adding a WHERE clause in the UPDATE statement inside the trigger avoids unnecessary updates and improves performance.