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.

Practice

1. Why are triggers needed in a PostgreSQL database?

easy

A. To automatically perform actions when data changes

B. To manually update data by user commands

C. To store large files outside the database

D. To create user accounts and manage permissions

Solution

1. Step 1: Understand the purpose of triggers

   Triggers run automatically when data changes, so they help automate tasks without manual intervention.

2. Step 2: Compare options to trigger function

   Options A, C, and D describe manual updates, file storage, and user management, which are not the main purpose of triggers.

3. Final Answer:

   To automatically perform actions when data changes -> Option A

4. Quick Check:

   Triggers automate tasks = To automatically perform actions when data changes [OK]

Hint: Triggers run automatically on data changes [OK]

Common Mistakes:

• Thinking triggers are for manual updates
• Confusing triggers with file storage
• Assuming triggers manage user permissions

2. Which of the following is the correct syntax to create a trigger in PostgreSQL?

easy

A. CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name();

B. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name();

C. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name();

D. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name();

Solution

1. Step 1: Review PostgreSQL trigger syntax

   The correct syntax uses CREATE TRIGGER, timing (BEFORE/AFTER), event (INSERT/UPDATE/DELETE), table, FOR EACH ROW or STATEMENT, and EXECUTE FUNCTION.

2. Step 2: Check each option for syntax correctness

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); matches the correct syntax exactly. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name(); uses CALL instead of EXECUTE FUNCTION, which is invalid. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name(); uses EXECUTE PROCEDURE, deprecated in modern PostgreSQL. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name(); places ON table_name before timing and event, which is incorrect order.

3. Final Answer:

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); -> Option A

4. Quick Check:

   Correct trigger syntax = CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); [OK]

Hint: Use EXECUTE FUNCTION, not CALL or PROCEDURE [OK]

Common Mistakes:

• Using CALL instead of EXECUTE FUNCTION
• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION
• Placing ON table_name in wrong position

3. Given this trigger function and trigger, what will happen when a new row is inserted into orders table?

CREATE FUNCTION check_stock() RETURNS trigger AS $$ BEGIN IF NEW.quantity > stock THEN RAISE EXCEPTION 'Not enough stock'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER stock_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_stock();

medium

A. The trigger will delete the row if quantity is too high

B. The insert will fail if quantity is greater than stock

C. The trigger will update the stock automatically

D. The insert will always succeed regardless of quantity

Solution

1. Step 1: Analyze the trigger function logic

   The function checks if NEW.quantity is greater than stock. If yes, it raises an exception to stop the insert.

2. Step 2: Understand trigger timing and effect

   The trigger runs BEFORE INSERT on orders. If exception is raised, insert fails. Otherwise, it returns NEW row to proceed.

3. Final Answer:

   The insert will fail if quantity is greater than stock -> Option B

4. Quick Check:

   Exception raised on high quantity = The insert will fail if quantity is greater than stock [OK]

Hint: Exception stops insert if condition met [OK]

Common Mistakes:

• Assuming trigger updates stock automatically
• Thinking insert always succeeds
• Believing trigger deletes rows

4. You created this trigger but it does not run when you insert data:

CREATE TRIGGER log_insert AFTER INSERT ON sales EXECUTE FUNCTION log_sale();

What is the likely problem?

medium

A. AFTER INSERT triggers cannot be created

B. Trigger function name is incorrect

C. Missing FOR EACH ROW clause in trigger definition

D. log_sale() function must return void

Solution

1. Step 1: Check trigger syntax requirements

   PostgreSQL requires FOR EACH ROW or FOR EACH STATEMENT clause in CREATE TRIGGER statement.

2. Step 2: Identify missing clause in given trigger

   The trigger lacks FOR EACH ROW or FOR EACH STATEMENT, so it is invalid and will not run.

3. Final Answer:

   Missing FOR EACH ROW clause in trigger definition -> Option C

4. Quick Check:

   Triggers need FOR EACH ROW/STATEMENT clause [OK]

Hint: Always include FOR EACH ROW or STATEMENT [OK]

Common Mistakes:

• Assuming AFTER INSERT triggers are disallowed
• Thinking function name causes trigger failure
• Believing trigger functions must return void

5. You want to keep a history of changes to the employees table automatically. Which trigger setup best achieves this?

hard

A. Create AFTER UPDATE trigger that drops employees table

B. Create AFTER DELETE trigger that updates employees table

C. Create BEFORE INSERT trigger that deletes old rows

D. Create BEFORE UPDATE trigger that inserts old row into history table

Solution

1. Step 1: Understand requirement to keep change history

   To keep history, old data must be saved before it changes or is deleted.

2. Step 2: Evaluate trigger options for history tracking

   Create BEFORE UPDATE trigger that inserts old row into history table uses BEFORE UPDATE trigger to save old row to history table, which is correct. Create AFTER DELETE trigger that updates employees table updates employees after delete, which is unrelated. Create BEFORE INSERT trigger that deletes old rows deletes old rows before insert, losing data. Create AFTER UPDATE trigger that drops employees table drops the table, which is destructive.

3. Final Answer:

   Create BEFORE UPDATE trigger that inserts old row into history table -> Option D

4. Quick Check:

   Save old data before update = Create BEFORE UPDATE trigger that inserts old row into history table [OK]

Hint: Save old row before update for history [OK]

Common Mistakes:

• Using AFTER DELETE to update main table
• Deleting old rows on insert
• Dropping table by mistake