AFTER DELETE Trigger — SQL Technical Interview Questions & Answers

AFTER DELETE Trigger

AFTER DELETE Trigger

  • A trigger is SQL that runs automatically on INSERT, UPDATE, or DELETE. CREATE TRIGGER trg BEFORE INSERT ON marks FOR EACH ROW SET NEW.score = IF(NEW.score < 0, 0, NEW.score); clamps negative scores.
  • BEFORE can change NEW values. AFTER sees the row already written. FOR EACH ROW is the MySQL student form. Don’t hide all business rules in triggers — apps become hard to debug.
  • OLD is the previous row on UPDATE/DELETE. NEW is the incoming row on INSERT/UPDATE.
  • Trap: trigger that INSERTs into the same table → loop. Trap: huge logic that belongs in the app.
  • On the example next to this theory: Create Trigger: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.
  • On the example next to this theory: Show Trigger: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.
  • On the example next to this theory: DROP Trigger: create two demo rows, then SELECT qty >= 2 ordered. Say which labels come back.
  • On the example next to this theory: Before Insert Trigger — iNSERT adds rows. List columns explicitly for clarity.
  • On the example next to this theory: After Insert Trigger — iNSERT adds rows. List columns explicitly for clarity.
  • On the example next to this theory: BEFORE DELETE Trigger — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).
  • On the example next to this theory: AFTER DELETE Trigger — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

Technical Interview Tip: BEFORE INSERT … FOR EACH ROW SET NEW.col = …

Code Examples for AFTER DELETE Trigger

AFTER DELETE Trigger — sample query

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT);
INSERT INTO tags VALUES (1, 'old'), (2, 'keep');
DELETE FROM tags WHERE label = 'old';
SELECT * FROM tags;

AFTER DELETE Trigger — dELETE removes matching rows. TRUNCATE clears a whole table faster (MySQL).

How to Explain AFTER DELETE Trigger to an Interviewer

When does a trigger run?

On INSERT, UPDATE, or DELETE of that table

NEW vs OLD?

NEW = incoming values OLD = previous row