CodeBlanks Logo Icon CodeBlanks
Login Register

BEFORE and AFTER

Learn the timing difference.

BEFORE and AFTER

BEFORE runs before the event, AFTER after. BEFORE for validation, AFTER for logging.

CREATE TRIGGER check_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000';
  END IF;
END;

This query checks before the update.

The trigger check_balance is created.

BEFORE and AFTER — details

A BEFORE trigger can modify data or stop the operation. AFTER only reads.

For example, BEFORE UPDATE can reject a negative balance; AFTER UPDATE is suited to adding a log entry once the change is written.

Practice BEFORE and AFTER

Write a BEFORE UPDATE trigger: block negative balance.

Starter code

query.sql
-- before trigger
CREATE TRIGGER check_balance

Sample solution

query.sql
CREATE TRIGGER check_balance
BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
  IF NEW.balance < 0 THEN
    SIGNAL SQLSTATE '45000';
  END IF;
END

Expected output

Trigger oluşturuldu

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 Add BEFORE

Question 
CREATE TRIGGER check_balance
____ UPDATE ON accounts
Answer 
CREATE TRIGGER check_balance
BEFORE UPDATE ON accounts

Explanation: A BEFORE UPDATE trigger runs before the update is written, so it can block an invalid value.

2 Which trigger for validation?

  • BEFORE Correct answer
  • AFTER
  • INSTEAD
  • DURING

Explanation: A BEFORE trigger is for validation.

3 BEFORE and AFTER: match the terms

Term Meaning
BEFORE before
AFTER after
validation check
logging record

All lessons in Advanced

1 CTE

  1. What is a CTE?
  2. Using WITH
  3. Multiple CTEs
  4. CTE vs subquery
  5. CTE with JOIN
  6. Summary - CTE

2 Recursive CTE

  1. What is a recursive CTE?
  2. Anchor and recursive parts
  3. Walk a hierarchy
  4. Tracking depth
  5. Stopping recursion
  6. Summary - Recursive CTE

3 Window functions

  1. What is a window function?
  2. ROW_NUMBER
  3. RANK and DENSE_RANK
  4. LAG and LEAD
  5. PARTITION BY
  6. Summary - Window functions

4 Project: Analytics report

  1. Analytics design
  2. CTE summary
  3. Top users with ROW_NUMBER
  4. Growth with LAG
  5. Final report

5 Pivot and unpivot

  1. What is pivot?
  2. Pivot with CASE
  3. FILTER clause
  4. Unpivot
  5. Dynamic pivot
  6. Summary - Pivot

6 Materialized view

  1. What is a materialized view?
  2. CREATE
  3. REFRESH
  4. View vs materialized view
  5. Index on materialized views
  6. Summary - Materialized view

7 Stored procedure

  1. What is a procedure?
  2. CREATE PROCEDURE
  3. Parameters
  4. CALL
  5. Control flow
  6. Summary - Stored procedure

8 Function

  1. What is a function?
  2. CREATE FUNCTION
  3. RETURN
  4. Calling a function
  5. Function vs procedure
  6. Summary - Function

9 Trigger

  1. What is a trigger?
  2. BEFORE and AFTER
  3. INSERT trigger
  4. UPDATE trigger
  5. Write an audit trigger
  6. Summary - Trigger

10 Transaction isolation

  1. What is isolation?
  2. READ UNCOMMITTED
  3. READ COMMITTED
  4. REPEATABLE READ
  5. SERIALIZABLE
  6. Summary - Isolation

11 Locking

  1. What is a lock?
  2. Shared and exclusive locks
  3. Deadlock
  4. SELECT FOR UPDATE
  5. Lock timeout
  6. Summary - Locking

12 Query optimization

  1. What is EXPLAIN?
  2. Seq scan vs index scan
  3. Cost
  4. Sargable
  5. N+1 problem
  6. Summary - Optimization

13 Partitioning

  1. What is partitioning?
  2. Range partition
  3. List partition
  4. Hash partition
  5. Partition pruning
  6. Summary - Partitioning

14 Advanced final

  1. Hierarchy report
  2. Window analysis
  3. Procedure and trigger
  4. Optimization