DELETE trigger in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of DELETE trigger after row deletion

Consider a table Employees with columns id and salary. A DELETE trigger is defined to insert the deleted row's id and salary into a log table DeletedEmployees. What will be the content of DeletedEmployees after deleting the employee with id = 3?

SQL

CREATE TABLE Employees (id INT PRIMARY KEY, salary INT);
CREATE TABLE DeletedEmployees (id INT, salary INT);

CREATE TRIGGER trg_after_delete
AFTER DELETE ON Employees
FOR EACH ROW
INSERT INTO DeletedEmployees (id, salary) VALUES (OLD.id, OLD.salary);

INSERT INTO Employees VALUES (1, 5000), (2, 6000), (3, 7000);

DELETE FROM Employees WHERE id = 3;

A

id | salary
3  | 7000

B

id | salary
1  | 5000
2  | 6000

C

id | salary
3  | NULL

D Empty table

💡 Hint

Think about what data the trigger captures when a row is deleted.

The DELETE trigger runs after a row is deleted. It uses OLD to access the deleted row's data. So the deleted employee's id and salary are inserted into DeletedEmployees. Only the deleted row (id=3) is logged.

🧠 Conceptual

intermediate

Understanding OLD and NEW in DELETE triggers

In a DELETE trigger, which of the following statements about OLD and NEW references is correct?

A Neither <code>OLD</code> nor <code>NEW</code> are available in DELETE triggers.

B <code>OLD</code> contains the row data before deletion; <code>NEW</code> is not available.

C Both <code>OLD</code> and <code>NEW</code> contain the row data before deletion.

D <code>NEW</code> contains the row data before deletion; <code>OLD</code> is not available.

💡 Hint

Think about what data exists after a row is deleted.

In DELETE triggers, OLD holds the data of the row being deleted. NEW is only available in INSERT or UPDATE triggers.

📝 Syntax

advanced

Identify the syntax error in DELETE trigger definition

Which option contains a syntax error in the DELETE trigger definition?

SQL

CREATE TRIGGER trg_del
AFTER DELETE ON Orders
FOR EACH ROW
BEGIN
  INSERT INTO DeletedOrders (order_id) VALUES (OLD.order_id);
END;

A CREATE TRIGGER trg_del AFTER DELETE ON Orders FOR EACH ROW BEGIN INSERT INTO DeletedOrders (order_id) VALUES (OLD.order_id); END

B CREATE TRIGGER trg_del AFTER DELETE ON Orders FOR EACH ROW INSERT INTO DeletedOrders (order_id) VALUES (OLD.order_id);

C CREATE TRIGGER trg_del AFTER DELETE ON Orders FOR EACH ROW BEGIN INSERT INTO DeletedOrders (order_id) VALUES (OLD.order_id); END;

D CREATE TRIGGER trg_del AFTER DELETE ON Orders FOR EACH ROW BEGIN INSERT INTO DeletedOrders (order_id) VALUES (NEW.order_id); END;

💡 Hint

Check the use of OLD and NEW in DELETE triggers.

Option D uses NEW.order_id in a DELETE trigger, which is invalid because NEW is not available in DELETE triggers.

❓ optimization

advanced

Optimizing DELETE trigger for bulk deletes

You have a DELETE trigger that logs deleted rows one by one. Which approach improves performance when deleting many rows at once?

A Use a statement-level trigger to insert all deleted rows in a single batch.

B Use a FOR EACH ROW trigger to insert each deleted row individually.

C Disable the trigger during bulk deletes and manually log rows later.

D Add a delay in the trigger to reduce server load.

💡 Hint

Think about how triggers handle multiple rows.

Statement-level triggers run once per DELETE statement and can handle all deleted rows together, improving performance over row-level triggers that run once per row.

🔧 Debug

expert

Why does this DELETE trigger cause an infinite loop?

Given the trigger below, deleting a row from Products causes an infinite loop. Why?

CREATE TRIGGER trg_del_product
AFTER DELETE ON Products
FOR EACH ROW
BEGIN
  DELETE FROM Products WHERE id = OLD.id;
END;

A The trigger tries to delete from a different table causing a deadlock.

B The trigger uses OLD.id which is not available in AFTER DELETE triggers.

C The trigger deletes the same row again, causing recursive trigger calls.

D The trigger syntax is invalid and causes repeated errors.

💡 Hint

Consider what happens when the trigger deletes rows inside itself.

The trigger deletes the same row that was just deleted, causing the trigger to fire again repeatedly, creating an infinite loop.