Chapter 22 Advanced 57 Questions

Practice Questions — Triggers and Scheduled Events

← Back to Notes

8 Easy

11 Medium

13 Hard

Topic-Specific Questions

Question 1

Easy

What is the difference between BEFORE and AFTER triggers?

Think about when the row is written.

BEFORE fires before the row is written to disk, allowing you to inspect and modify NEW. AFTER fires once the row has been written — NEW is read-only. Use BEFORE for validation and defaulting; AFTER for logging and cascading changes to other tables.

Question 2

Easy

Which pseudo-records are available in a BEFORE UPDATE trigger?

Think previous row vs new row.

Both OLD (the current row about to be changed) and NEW (the row after the UPDATE is applied). In a BEFORE trigger, NEW is writable, so you can alter the outgoing row.

Question 3

Easy

Given this trigger, what does SELECT email FROM users; return after INSERT INTO users(email) VALUES (' Aarav@Mail.COM ');?

CREATE TRIGGER clean BEFORE INSERT ON users
FOR EACH ROW SET NEW.email = LOWER(TRIM(NEW.email));

The trigger normalises the email.

aarav@mail.com

Question 4

Easy

Can a DELETE trigger access NEW?

Think about whether a deleted row has a new version.

No. DELETE triggers only have OLD. There is no NEW row because the row is being removed, not replaced. Trying to read NEW in a DELETE trigger is a syntax error.

Question 5

Easy

What is a scheduled event in MySQL?

Compare with triggers.

A named, time-based task stored inside the database. It runs on the event-scheduler thread, not in response to any row change. Used for daily cleanup, archival, hourly cache refresh, and similar chores.

Question 6

Easy

What enables the event scheduler?

One setting.

SET GLOBAL event_scheduler = ON; or permanently via event_scheduler=ON in my.cnf.

Question 7

Medium

A trigger counts how many times it has fired:

CREATE TRIGGER bump
AFTER INSERT ON orders
FOR EACH ROW
UPDATE stats SET n = n + 1 WHERE id = 1;

-- stats.n starts at 0
INSERT INTO orders(total) VALUES (10),(20),(30);
SELECT n FROM stats WHERE id = 1;

How many rows does a 3-row INSERT create?

3

Question 8

Medium

Why can MySQL refuse to let a trigger update its own table?

Think infinite recursion.

Because allowing it would invite infinite recursion: the trigger fires on UPDATE, modifies a row, which fires the trigger again. MySQL's default is to block any trigger that would re-trigger itself, raising ERROR 1442.

Question 9

Medium

When would you prefer a column default (DEFAULT CURRENT_TIMESTAMP) over a BEFORE INSERT trigger?

Think simplicity.

Always, when a simple default is enough. Column defaults are simpler, well-documented, and faster. Reach for a trigger only when the default depends on other columns or requires conditional logic.

Question 10

Medium

What does this event do, assuming orders contains rows from 2024 and 2025, and today is 2026-04-16?

CREATE EVENT archive_old
ON SCHEDULE EVERY 1 MONTH
STARTS '2026-05-01'
DO
BEGIN
    INSERT INTO orders_archive SELECT * FROM orders
    WHERE created_at < NOW() - INTERVAL 1 YEAR;
    DELETE FROM orders
    WHERE created_at < NOW() - INTERVAL 1 YEAR;
END;

Rows older than 1 year move to archive.

Each month it copies rows older than one year from orders into orders_archive and deletes them from orders. On 2026-05-01 it archives anything from before 2025-05-01.

Question 11

Medium

What does SIGNAL SQLSTATE '45000' do?

Think user-defined errors.

Raises a user-defined SQL exception with SQLSTATE 45000 (the reserved class for unhandled user errors). Inside a BEFORE trigger this aborts the triggering INSERT/UPDATE/DELETE and the caller receives error 1644 with the MESSAGE_TEXT you set.

Question 12

Medium

Why is FOR EACH ROW the only option in MySQL?

Compare to Oracle/PostgreSQL statement triggers.

MySQL does not support statement-level triggers — those fire once per statement regardless of rows affected. Every MySQL trigger fires once per affected row. If you need statement-level behaviour, you must emulate it using session variables or call logic from application code.

Question 13

Hard

Two AFTER INSERT triggers exist on the same table. In what order do they fire, and how do you change that order?

Think about creation order and the FOLLOWS/PRECEDES clauses.

By default, MySQL fires them in the order they were created. To control the order explicitly, add FOLLOWS other_trigger or PRECEDES other_trigger when creating the second trigger. This writes an explicit ordering into information_schema.

Question 14

Hard

How do triggers behave under row-based vs statement-based replication?

Row-based replicates row changes; statement-based replays the SQL.

Under row-based replication (the recommended setting), trigger side effects on the master are captured as row changes in the binary log and applied on the replica without re-running the trigger. Under statement-based replication, the replica re-executes the original statement, and its triggers fire independently — dangerous if any trigger is non-deterministic.

Question 15

Hard

Your event runs a 2-hour job every hour. What happens?

Think overlap.

By default MySQL will not queue overlapping invocations — if the previous run is still executing when the next fire time comes, the new invocation is skipped silently. You can confirm by inspecting information_schema.EVENTS.LAST_EXECUTED. In practice, either make the job idempotent, run it less often, or use ON COMPLETION PRESERVE and redesign.

Question 16

Hard

A junior dev wants to put 'send welcome email' logic in an AFTER INSERT trigger on users. What do you tell them?

Think about transactionality and external side effects.

Don't. Triggers run inside the INSERT transaction. Email is an external side effect — if the trigger succeeds but the transaction later rolls back, you've emailed someone who was never created. The right pattern is to INSERT a row into a notifications_outbox table from the trigger; a separate worker reads the outbox and sends emails. The outbox insert is rolled back if the transaction fails.

Question 17

Hard

How can you inspect the last execution time of an event?

Information schema.

SELECT EVENT_NAME, LAST_EXECUTED, STATUS FROM information_schema.EVENTS WHERE EVENT_SCHEMA = 'shop';. The column LAST_EXECUTED gives the last fire time (NULL if never executed).

Mixed & Application Questions

Question 1

Easy

If stats.n starts at 0 and

CREATE TRIGGER bump AFTER INSERT ON t
FOR EACH ROW UPDATE stats SET n = n + 1;

and we run INSERT INTO t VALUES (1); then INSERT INTO t VALUES (2),(3),(4);, what is n?

One row + three rows.

4

Question 2

Easy

List three INSERT/UPDATE/DELETE trigger events that can have a BEFORE variant.

All of them can.

BEFORE INSERT, BEFORE UPDATE, BEFORE DELETE. (Similarly for AFTER.) Six combinations total per table.

Question 3

Medium

What does this return?

CREATE TRIGGER t_trg BEFORE INSERT ON t
FOR EACH ROW SET NEW.x = NEW.x * 2;

INSERT INTO t(x) VALUES (5);
SELECT x FROM t;

The trigger doubles x.

10

Question 4

Medium

Why is the transactional outbox pattern better than sending emails directly from an AFTER INSERT trigger?

Think about rollback.

Triggers run inside the transaction. If the surrounding transaction rolls back after the trigger fired, a direct email has already been sent — you cannot un-email someone. The outbox row, however, is rolled back with the rest of the transaction, keeping data and side effects in lockstep. A separate worker reads committed outbox rows and sends mail.

Question 5

Medium

What does SHOW TRIGGERS FROM shop LIKE 'emp%' return?

Filter by table name.

All triggers in database shop whose table name matches the LIKE pattern 'emp%' — e.g. triggers on employees and employee_salaries. Each row shows Trigger, Event, Table, Statement, Timing (BEFORE/AFTER), Created, and more.

Question 6

Medium

Give one reason to prefer an event over cron and one reason to prefer cron.

Think location and monitoring.

Prefer events when the task is entirely database-internal and simple (nightly DELETE of old rows) — no extra infrastructure. Prefer cron (or Airflow) when you need robust retries, alerting on failure, orchestration across multiple databases, or logs outside the MySQL error log.

Question 7

Medium

After this DELETE, how many rows in history?

CREATE TRIGGER t AFTER DELETE ON orders
FOR EACH ROW INSERT INTO orders_history VALUES (OLD.id, OLD.total);

-- orders has 5 rows: ids 1..5
DELETE FROM orders WHERE id > 2;

Rows 3, 4, 5 are deleted.

3

Question 8

Hard

How do you make an event run exactly once and then disappear?

AT + ON COMPLETION.

CREATE EVENT one_off ON SCHEDULE AT '2026-05-01 02:00:00' ON COMPLETION NOT PRESERVE DO ...; AT fires once at the specific time. ON COMPLETION NOT PRESERVE (the default) drops the event after completion. Use ON COMPLETION PRESERVE if you want it to remain in a COMPLETED state for audit.

Question 9

Hard

You have triggers log_ins (inserted date) and verify_ins (validation). Which should fire first, and how do you enforce it?

Validate before logging.

verify_ins must fire first — you do not want to log rows that will be rejected. Make verify_ins a BEFORE INSERT trigger (it raises SIGNAL on invalid rows) and log_ins an AFTER INSERT trigger. If both must be AFTER INSERT, use the FOLLOWS clause: CREATE TRIGGER log_ins AFTER INSERT ON t FOR EACH ROW FOLLOWS verify_ins ....

Question 10

Hard

What happens here?

CREATE TRIGGER strict_balance BEFORE UPDATE ON accounts
FOR EACH ROW
BEGIN
    IF NEW.balance < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Negative balance';
    END IF;
END;

-- accounts: (101, 500)
UPDATE accounts SET balance = balance - 600 WHERE id = 101;

NEW.balance would be -100.

The UPDATE is rejected with ERROR 1644 (45000): Negative balance. The row remains at 500.

Question 11

Hard

Why is the default event behaviour NOT to queue missed runs?

Think about storms.

If the server was down for 6 hours and the event runs every minute, queuing would fire 360 invocations the moment the server came back — a stampede. MySQL's policy of "skip missed runs and continue from the next scheduled time" is safer: the job runs at the next natural tick.

Question 12

Hard

A trigger on orders UPDATEs inventory, which has its own trigger that UPDATEs stock_alerts. Is this cascade safe?

Think depth and hidden behaviour.

It is technically safe (no recursion on the same table) but a maintainability smell. The first developer to read the codebase will see an INSERT into orders and have no idea three tables change as a consequence. Document trigger chains explicitly, or — better — move the cascade into a stored procedure the application calls deliberately.

Question 13

Hard

ALTER EVENT rebuild_daily DISABLE; — what happens next Monday at the scheduled time?

Disabled events do not fire.

Nothing. The event remains in the database (visible in SHOW EVENTS with Status = DISABLED) but the scheduler skips it. Re-enable with ALTER EVENT rebuild_daily ENABLE.

Question 14

Hard

Your audit trigger is slow under bulk loads. Name two optimisations.

Batching and narrow scope.

(1) Only log rows that actually changed: IF NEW.col <> OLD.col THEN ... END IF; — no-op UPDATEs produce no audit rows. (2) Push trigger-generated rows into a simple staging table (no indexes), and batch-move them to the main audit table from a nightly event. This trades immediate-visibility for lower write amplification.

Question 15

Hard

Can you have two BEFORE INSERT triggers on the same table?

Yes, since MySQL 5.7.

Yes. Since MySQL 5.7 you can define multiple triggers with the same timing and event. Use FOLLOWS or PRECEDES to control the order in which they fire. Pre-5.7 only one trigger per timing+event was allowed.

Multiple Choice Questions

MCQ 1

What is the correct syntax prefix to begin a trigger definition?

A. MAKE TRIGGER ...
B. CREATE TRIGGER trigger_name BEFORE/AFTER event ON table
C. DEFINE TRIGGER trigger_name
D. ADD TRIGGER ...

Answer: B
B is correct. The full syntax is CREATE TRIGGER name { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON table FOR EACH ROW body.

MCQ 2

Which references are available in a BEFORE UPDATE trigger?

A. Only NEW
B. Only OLD
C. Both OLD and NEW
D. Neither

Answer: C
C is correct. UPDATE triggers see both: OLD is the pre-update row, NEW is the post-update row (writable only in BEFORE).

MCQ 3

Which reference exists in a DELETE trigger?

A. NEW
B. OLD
C. Both
D. Neither

Answer: B
B is correct. DELETE has only OLD; there is no new version of a deleted row.

MCQ 4

What is event_scheduler?

A. A MySQL thread that runs scheduled events
B. A stored procedure
C. A table
D. An index type

Answer: A
A is correct. It is the background thread that wakes up and executes events at their scheduled times. Must be ON (SET GLOBAL event_scheduler = ON).

MCQ 5

Which command lists triggers in the current database?

A. LIST TRIGGERS
B. SHOW TRIGGERS
C. DESCRIBE TRIGGERS
D. INFO TRIGGERS

Answer: B
B is correct. SHOW TRIGGERS returns name, event, table, timing, and body for each trigger. Optional LIKE filters by table name.

MCQ 6

You want a trigger that rejects negative salaries. Which timing and event?

A. AFTER INSERT
B. BEFORE INSERT and BEFORE UPDATE (both) with SIGNAL
C. AFTER DELETE
D. INSTEAD OF INSERT

Answer: B
B is correct. You must block both INSERTs and UPDATEs that would violate the rule. BEFORE lets SIGNAL abort the statement cleanly. MySQL has no INSTEAD OF triggers.

MCQ 7

How many times does a trigger fire for UPDATE employees SET salary = salary * 1.1 on a 1000-row table?

A. Once
B. Twice
C. 1000 times — FOR EACH ROW
D. Depends on the index

Answer: C
C is correct. MySQL triggers are row-level. 1000 rows updated = 1000 trigger invocations.

MCQ 8

What does SIGNAL SQLSTATE '45000' do in a BEFORE trigger?

A. Logs a warning only
B. Commits the transaction
C. Raises a user-defined error and aborts the triggering statement
D. Replicates the error to all servers

Answer: C
C is correct. SQLSTATE 45000 is the reserved class for user-defined unhandled exceptions. The UPDATE/INSERT/DELETE is aborted and the caller receives error 1644.

MCQ 9

Which statement about AFTER triggers is TRUE?

A. They can modify NEW
B. They see NEW as read-only; the row is already written
C. They fire before any constraint check
D. They can roll back their own changes

Answer: B
B is correct. After the row is written, NEW is immutable. Constraints are checked before AFTER triggers fire.

MCQ 10

Which command drops a trigger safely?

A. DROP TRIGGER name
B. DROP TRIGGER IF EXISTS name
C. DELETE TRIGGER name
D. ALTER TRIGGER name DROP

Answer: B
B is correct. IF EXISTS makes migrations idempotent — no error if the trigger was already dropped.

MCQ 11

Which schedule clause makes an event fire every day at 2 a.m. starting next Monday?

A. ON SCHEDULE AT '2026-04-20 02:00:00'
B. ON SCHEDULE EVERY 1 DAY STARTS '2026-04-20 02:00:00'
C. ON SCHEDULE DAILY 02:00
D. ON SCHEDULE EVERY HOUR

Answer: B
B is correct. EVERY 1 DAY is the interval; STARTS sets the first fire time.

MCQ 12

Which clause lets an event remain after it finishes a one-off run?

A. ON COMPLETION PRESERVE
B. ON COMPLETION DROP
C. ON COMPLETION FREEZE
D. ON COMPLETION KEEP

Answer: A
A is correct. The default is NOT PRESERVE — the event is dropped once done. PRESERVE keeps it with Status = DISABLED for audit.

MCQ 13

You have two AFTER INSERT triggers on the same table. What determines their firing order?

A. Alphabetical order of trigger names
B. Definition order, or FOLLOWS/PRECEDES clauses
C. Random
D. Smallest trigger id first

Answer: B
B is correct. MySQL 5.7+ uses definition order by default; FOLLOWS/PRECEDES override it for explicit control.

MCQ 14

Which pattern keeps emails from being sent for rolled-back transactions?

A. Sending directly from an AFTER INSERT trigger
B. The transactional outbox pattern (insert into a queue table, send later)
C. Using autocommit
D. Stored functions with MODIFIES SQL DATA

Answer: B
B is correct. The outbox row is part of the transaction; rollback un-queues it. A separate worker emails only committed rows.

MCQ 15

What happens when the event scheduler's run overlaps a previous run?

A. The new invocation is queued
B. The new invocation is skipped; LAST_EXECUTED is not updated
C. The server crashes
D. MySQL spawns a second worker per event

Answer: B
B is correct. MySQL does not run overlapping instances of the same event. Long-running events can fall behind — monitor LAST_EXECUTED.

MCQ 16

Which is TRUE about triggers under row-based replication?

A. Triggers re-execute on the replica
B. Row changes (including those made by the trigger) are shipped directly to the replica — triggers do NOT re-execute there
C. Triggers are disabled on replicas
D. Triggers fire twice

Answer: B
B is correct. Row-based replication ships the result of the trigger, not the statement. This makes non-deterministic triggers safe on replicas.

MCQ 17

What is the biggest risk of putting heavy business logic in triggers?

A. Triggers cannot access NEW.* columns
B. Logic becomes invisible to application developers and hard to debug
C. Triggers use more memory than stored procedures
D. Triggers cannot use transactions

Answer: B
B is correct. A reader of the application sees INSERT but has no idea the database also fires five triggers and updates four other tables. Name triggers clearly, document them, and push complex flows into named procedures the application calls explicitly.

Coding Challenges

Challenge 1: Audit Every Salary Update

Easy

Create an AFTER UPDATE trigger on employees that writes a row to salary_audit(emp_id, old_salary, new_salary, changed_at) only when salary actually changes.

Sample Input

employees: (1,'Aarav',50000) UPDATE employees SET salary=55000 WHERE id=1; UPDATE employees SET name='Aarav S' WHERE id=1;

Sample Output

salary_audit has ONE row (from the first update). The second UPDATE did not change salary, so the trigger did not log it.

Use IF NEW.salary <> OLD.salary. Use NOW() for changed_at.

DELIMITER $
CREATE TRIGGER audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO salary_audit(emp_id, old_salary, new_salary, changed_at)
        VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
    END IF;
END$
DELIMITER ;

Challenge 2: Normalise Email on Insert

Easy

Create a BEFORE INSERT trigger on users that lowercases and trims the email before storing it.

Sample Input

INSERT INTO users(email) VALUES (' Priya@Mail.COM ');

Sample Output

users row has email = 'priya@mail.com'.

Use SET NEW.email = LOWER(TRIM(NEW.email)).

DELIMITER $
CREATE TRIGGER normalise_email
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.email = LOWER(TRIM(NEW.email))$
DELIMITER ;

Challenge 3: Reject Negative Prices

Easy

Create a BEFORE INSERT and BEFORE UPDATE trigger on products that rejects any row with price < 0 using SIGNAL.

Sample Input

INSERT INTO products(name, price) VALUES ('Pen', -5);

Sample Output

ERROR 1644 (45000): Price cannot be negative

Use two separate triggers (INSERT and UPDATE) with the same check, or use SIGNAL SQLSTATE '45000'.

DELIMITER $
CREATE TRIGGER no_neg_price_ins
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END$

CREATE TRIGGER no_neg_price_upd
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Price cannot be negative';
    END IF;
END$
DELIMITER ;

Challenge 4: Denormalised Post Count

Medium

Write three triggers on posts that keep categories.post_count accurate: AFTER INSERT (increment), AFTER DELETE (decrement), AFTER UPDATE (handle category moves).

Sample Input

categories: (1,'Tech',0), (2,'Food',0) INSERT INTO posts(title, category_id) VALUES ('x',1),('y',1),('z',2); UPDATE posts SET category_id=2 WHERE title='y'; DELETE FROM posts WHERE title='z';

Sample Output

categories: (1,'Tech',1), (2,'Food',1)

All three triggers. On UPDATE, only act when category_id actually changed.

DELIMITER $
CREATE TRIGGER posts_inc AFTER INSERT ON posts
FOR EACH ROW
UPDATE categories SET post_count = post_count + 1 WHERE id = NEW.category_id$

CREATE TRIGGER posts_dec AFTER DELETE ON posts
FOR EACH ROW
UPDATE categories SET post_count = post_count - 1 WHERE id = OLD.category_id$

CREATE TRIGGER posts_move AFTER UPDATE ON posts
FOR EACH ROW
BEGIN
    IF NEW.category_id <> OLD.category_id THEN
        UPDATE categories SET post_count = post_count - 1 WHERE id = OLD.category_id;
        UPDATE categories SET post_count = post_count + 1 WHERE id = NEW.category_id;
    END IF;
END$
DELIMITER ;

Challenge 5: Archive-on-Delete Trigger

Medium

Write an AFTER DELETE trigger on customers that copies every deleted row into customers_archive with a deleted_at timestamp.

Sample Input

customers: (1,'Aarav','a@x'), (2,'Priya','p@x') DELETE FROM customers WHERE id=1;

Sample Output

customers_archive: (1,'Aarav','a@x', NOW())

Use OLD.* in the INSERT. Store NOW() for deleted_at.

DELIMITER $
CREATE TRIGGER archive_customer
AFTER DELETE ON customers
FOR EACH ROW
INSERT INTO customers_archive(id, name, email, deleted_at)
VALUES (OLD.id, OLD.name, OLD.email, NOW())$
DELIMITER ;

Challenge 6: Daily Soft-Delete Cleanup Event

Medium

Create an event that runs every day at 03:00 and deletes rows from customers where deleted_at IS NOT NULL and deleted_at < NOW() - INTERVAL 30 DAY.

Sample Input

(No input — event runs on schedule.)

Sample Output

After many days, customers soft-deleted more than 30 days ago are physically removed.

Use ON SCHEDULE EVERY 1 DAY STARTS '...'. Remember SET GLOBAL event_scheduler = ON.

SET GLOBAL event_scheduler = ON;

CREATE EVENT cleanup_soft_deleted
ON SCHEDULE EVERY 1 DAY
STARTS '2026-04-17 03:00:00'
COMMENT 'Remove soft-deleted customers older than 30 days'
DO
    DELETE FROM customers
    WHERE deleted_at IS NOT NULL
      AND deleted_at < NOW() - INTERVAL 30 DAY;

Challenge 7: Monthly Archival Event

Hard

Create an event that runs on the 1st of every month at 01:00 to move rows from orders older than 12 months into orders_archive, then delete them from orders. Wrap both statements in a transaction so either both succeed or both roll back.

Sample Input

orders has entries from 2024-01-01 to now.

Sample Output

Each month, 12-month-old rows move to orders_archive.

Use EVERY 1 MONTH. Use START TRANSACTION / COMMIT inside a procedure called by the event.

DELIMITER $
CREATE PROCEDURE archive_old_orders()
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;

    START TRANSACTION;
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE created_at < NOW() - INTERVAL 12 MONTH;

    DELETE FROM orders
    WHERE created_at < NOW() - INTERVAL 12 MONTH;
    COMMIT;
END$
DELIMITER ;

SET GLOBAL event_scheduler = ON;

CREATE EVENT monthly_archive
ON SCHEDULE EVERY 1 MONTH
STARTS '2026-05-01 01:00:00'
DO CALL archive_old_orders();

Challenge 8: Guard a Locked Invoice

Hard

Write a BEFORE UPDATE trigger on invoices that forbids any change to amount once status = 'LOCKED'. Allow status transitions (LOCKED -> PAID) but reject amount edits with a clear error.

Sample Input

invoices: (1001, 5000, 'LOCKED') UPDATE invoices SET amount=6000 WHERE id=1001; -- should fail UPDATE invoices SET status='PAID' WHERE id=1001; -- should pass

Sample Output

First UPDATE: ERROR 1644 (45000): Cannot change amount on a LOCKED invoice. Second UPDATE: Query OK, 1 row affected.

Compare OLD.status and OLD.amount vs NEW.amount.

DELIMITER $
CREATE TRIGGER protect_locked_invoice
BEFORE UPDATE ON invoices
FOR EACH ROW
BEGIN
    IF OLD.status = 'LOCKED' AND NEW.amount <> OLD.amount THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Cannot change amount on a LOCKED invoice';
    END IF;
END$
DELIMITER ;

Need to Review the Concepts?

Go back to the detailed notes for this chapter.

Read Chapter Notes

Want to master SQL and databases with a mentor?

Explore our MySQL Masterclass