Integrity with triggers
- SQL course
- Intermediate
- Lesson 98/115
- 4 min
Add extra integrity rules with triggers.
Integrity with triggers
A trigger is code that runs automatically on an event. Used for custom integrity checks.
CREATE TRIGGER check_balance
BEFORE INSERT ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000';
END IF;
END;This query prevents inserting a negative balance.
The trigger check_balance is created.
Integrity with triggers — details
A trigger checks more complex conditions than CHECK. It can relate multiple tables.
For example, rejecting an order when it exceeds the customer's credit limit stored in another table is done with a trigger, not with CHECK.
Practice Integrity with triggers
Add a BEFORE INSERT trigger on accounts: block negative balance.
Starter code
-- trigger integrity
CREATE TRIGGER check_balanceSample solution
CREATE TRIGGER check_balance
BEFORE INSERT ON accounts
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
SIGNAL SQLSTATE '45000';
END IF;
ENDExpected 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
CREATE TRIGGER check_balance
____ INSERT ON accountsCREATE TRIGGER check_balance
BEFORE INSERT ON accountsExplanation: A BEFORE INSERT trigger runs before the row is written, so it can block bad data.
2 Integrity with triggers: match the terms
| Term | Meaning |
|---|---|
trigger |
trigger |
BEFORE |
before |
AFTER |
after |
automatic |
event |
3 When does a trigger run?
- Automatically, on an event Correct answer
- When called manually
- Always
- Never
Explanation: A trigger runs automatically on an event.