Why triggers are needed in PostgreSQL - Visual Breakdown

Concept Flow - Why triggers are needed

Data Change Event

↓

Trigger Activated?

↓

Trigger Function

↓

Perform Additional Logic

↓

Complete Data Operation

When data changes, the system checks if a trigger is set. If yes, it runs extra logic automatically before or after the change.

Execution Sample

PostgreSQL

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_modified_column();

This trigger updates a timestamp column automatically whenever an employee record is updated.

Execution Table

Step	Event	Trigger Check	Trigger Fired?	Action Taken	Result
1	Update employee record	Is trigger set on employees update?	Yes	Call update_modified_column()	Timestamp updated
2	Update employee record	Is trigger set on employees update?	Yes	Call update_modified_column()	Timestamp updated
3	Insert new employee	Is trigger set on employees insert?	No	No trigger action	Record inserted without timestamp update
4	Delete employee record	Is trigger set on employees delete?	No	No trigger action	Record deleted
5	Update employee record	Is trigger set on employees update?	Yes	Call update_modified_column()	Timestamp updated
6	End of operations	-	-	-	No more events

💡 No more data change events to process, execution ends.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
employee_record.modified_timestamp	NULL	2024-06-01 10:00	2024-06-01 10:05	NULL	NULL	2024-06-01 10:10	2024-06-01 10:10

Key Moments - 2 Insights

Why does the trigger only fire on update but not on insert or delete?

Because the trigger is defined specifically for the UPDATE event on the employees table, as shown in execution_table rows 1, 2, and 5 where it fires, and rows 3 and 4 where it does not.

What happens if there is no trigger defined for an event?

No extra action occurs; the data operation proceeds normally without running any trigger function, as seen in execution_table rows 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of modified_timestamp after step 2?

A NULL

B 2024-06-01 10:05

C 2024-06-01 10:00

D 2024-06-01 10:10

💡 Hint

Check variable_tracker column 'After 2' for employee_record.modified_timestamp.

At which step does the trigger NOT fire during an employee record update?

A Step 5

B Step 4

C None, it fires on all updates

D Step 3

💡 Hint

Look at execution_table rows where event is 'Update employee record' and see if trigger fired.

If the trigger was also set for INSERT events, what would change in the execution_table?

A Row 3 would show trigger fired and timestamp updated

B Row 4 would show trigger fired

C Row 1 would not fire trigger

D No changes would occur

💡 Hint

Check row 3 where insert event currently has no trigger action.

Concept Snapshot

Triggers run extra code automatically when data changes.
They can be set BEFORE or AFTER INSERT, UPDATE, DELETE.
Triggers help keep data consistent or enforce rules.
Without triggers, you must manually run extra code.
Triggers save time and reduce errors by automating tasks.

Full Transcript

Triggers are special database features that run automatically when data changes. When you update, insert, or delete data, the database checks if a trigger is set for that event. If yes, it runs the trigger's function to do extra work like updating timestamps or checking rules. This automation helps keep data accurate and consistent without needing manual steps. For example, a trigger on the employees table updates a modified timestamp whenever a record changes. If no trigger is set for an event, the database just performs the data change normally. This visual shows how triggers activate on updates but not on inserts or deletes unless defined. Triggers save time and prevent mistakes by running important code automatically.