Why triggers are needed in SQL - Visual Breakdown

Concept Flow - Why triggers are needed

Data Change Event

↓

Trigger Activated?

No→No Action

Yes↓

Trigger Code Runs

↓

Automatic Action Performed

↓

Data Updated with Extra Logic

↓

End

When data changes, the system checks if a trigger applies. If yes, it runs extra code automatically to keep data correct or do extra tasks.

Execution Sample

SQL

CREATE TRIGGER update_stock
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
  UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;

This trigger runs after a sale is recorded to reduce the stock of the sold product automatically.

Execution Table

Step	Event	Trigger Check	Trigger Action	Data State Change
1	Insert new sale record	Check if 'update_stock' trigger applies	Yes, trigger activates	Sale record added, stock update starts
2	Trigger runs	N/A	Update product stock by subtracting sold quantity	Product stock reduced accordingly
3	Finish trigger	N/A	Trigger ends	Data consistent with sale recorded
4	Next operation	No trigger	No action	Data unchanged

💡 No more triggers to run after stock update; data is consistent.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
sale_record	None	Inserted with quantity=3, product_id=101	Unchanged	Unchanged	Unchanged
product_stock	50	50	47 (50 - 3)	47	47

Key Moments - 3 Insights

Why does the trigger run automatically after inserting a sale?

Because the trigger is defined to activate AFTER INSERT on the sales table, as shown in execution_table step 1 where the trigger check is 'Yes'.

What happens if there was no trigger for stock update?

The stock would not change automatically, leading to incorrect data. The trigger ensures stock stays accurate without manual updates (see execution_table step 2).

Can triggers cause changes to other tables?

Yes, triggers can update other tables automatically, like updating the products table stock after a sale (execution_table step 2).

Visual Quiz - 3 Questions

Look at the execution_table, what happens at step 2?

A The trigger updates the product stock by subtracting the sold quantity

B The sale record is deleted

C No trigger runs

D The stock is increased

💡 Hint

Check the 'Trigger Action' and 'Data State Change' columns at step 2 in execution_table

At which step does the trigger check decide not to run?

A Step 1

B Step 3

C Step 4

D Step 2

💡 Hint

Look for 'No trigger' in the 'Trigger Check' column in execution_table

If the sale quantity was 5 instead of 3, what would be the product_stock after step 2?

A 47

B 45

C 50

D 55

💡 Hint

Refer to variable_tracker product_stock changes and subtract quantity from initial stock

Concept Snapshot

Triggers run automatically when data changes.
They perform extra actions like updating related data.
Defined to run BEFORE or AFTER events like INSERT, UPDATE, DELETE.
Help keep data consistent without manual steps.
Useful for enforcing rules and automating tasks.

Full Transcript

Triggers are special database rules that run automatically when data changes. For example, after adding a sale record, a trigger can update the product stock to subtract the sold quantity. This automatic action helps keep data accurate and consistent without needing manual updates. The flow starts with a data change event, checks if a trigger applies, runs the trigger code if yes, performs the automatic action, and ends with updated data. This process ensures related data stays correct and business rules are enforced smoothly.