Think of a home alarm system. You do not press a button every time someone opens the door. The alarm reacts automatically. Door opens? Alarm fires. Motion detected? Camera starts recording. You set up the rule once, and it runs by itself forever.
A database trigger works the same way. It is SQL code that fires automatically when a specific event happens on a table — an INSERT, UPDATE, or DELETE. You do not call it. You do not invoke it. It just watches and reacts.
| Event | OLD Available? | NEW Available? | What They Contain |
|---|---|---|---|
| INSERT | No | Yes | NEW = the row being inserted |
| UPDATE | Yes | Yes | OLD = row before change, NEW = row after change |
| DELETE | Yes | No | OLD = the row being deleted |
A common beginner mistake: trying to access OLD in an INSERT trigger or NEW in a DELETE trigger. There is no "old" row in an INSERT — the row did not exist before. Similarly, there is no "new" row in a DELETE — the row will not exist after.
Q: What is the difference between BEFORE and AFTER triggers? When would you use each?
A: BEFORE triggers fire before the data change is committed. They can modify the incoming data (SET NEW.column = value) or reject the operation entirely (SIGNAL SQLSTATE). Use BEFORE for validation and auto-correction — for example, forcing an email to lowercase before INSERT. AFTER triggers fire after the change is saved. They cannot modify the data but can read both OLD and NEW values. Use AFTER for audit logging, notifications, and cascade updates — for example, logging the old and new balance after an UPDATE.
Key Point: Triggers are automatic reactions to database events. BEFORE triggers can validate and modify data. AFTER triggers are used for audit logging and side effects. They use OLD and NEW to access row data.