Why triggers are needed in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that automatically updates the 'last_modified' column when a row is updated.

SQL

CREATE TRIGGER update_timestamp BEFORE UPDATE ON employees FOR EACH ROW SET NEW.last_modified = [1];

Drag options to blanks, or click blank then click option'

ANOW()

BGETDATE()

CSYSDATE()

DCURRENT_TIMESTAMP

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function that is not supported in the SQL dialect.

Forgetting to update the NEW row in the trigger.

The NOW() function returns the current date and time in SQL, which is used here to update the 'last_modified' column automatically.

2fill in blank

medium

Complete the code to create a trigger that prevents deletion of rows from the 'orders' table.

SQL

CREATE TRIGGER prevent_delete BEFORE DELETE ON orders FOR EACH ROW BEGIN SIGNAL SQLSTATE [1] SET MESSAGE_TEXT = 'Deletion not allowed'; END;

Drag options to blanks, or click blank then click option'

A'23000'

B'45000'

C'42000'

D'HY000'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a SQLSTATE code that does not represent user-defined exceptions.

Not using SIGNAL to raise an error.

The SQLSTATE code '45000' is a generic user-defined exception used to signal an error in triggers.

3fill in blank

hard

Fix the error in the trigger code that should log insertions into the 'audit_log' table.

SQL

CREATE TRIGGER log_insert AFTER INSERT ON products FOR EACH ROW INSERT INTO audit_log (product_id, action) VALUES ([1], 'INSERT');

Drag options to blanks, or click blank then click option'

ANEW.product_id

BOLD.product_id

Cproduct_id

DNEW.id

Attempts:

3 left

💡 Hint

Common Mistakes

Using OLD in an insert trigger.

Using a column name without prefixing NEW or OLD.

In an AFTER INSERT trigger, the NEW keyword refers to the newly inserted row. So, NEW.product_id correctly accesses the inserted product's ID.

4fill in blank

hard

Fill both blanks to create a trigger that updates stock quantity after a sale is inserted.

SQL

CREATE TRIGGER update_stock AFTER INSERT ON sales FOR EACH ROW BEGIN UPDATE products SET stock = stock [1] NEW.quantity WHERE product_id = [2]; END;

Drag options to blanks, or click blank then click option'

A-

B+

CNEW.product_id

DOLD.product_id

Attempts:

3 left

💡 Hint

Common Mistakes

Using '+' instead of '-' to update stock.

Using OLD.product_id which is not available in insert triggers.

The trigger subtracts the sold quantity from the stock, so the operator is -. The product to update is identified by NEW.product_id from the inserted sale.

5fill in blank

hard

Fill all three blanks to create a trigger that prevents negative stock after an update.

SQL

CREATE TRIGGER check_stock BEFORE UPDATE ON products FOR EACH ROW BEGIN IF NEW.stock [1] [2] THEN SIGNAL SQLSTATE [3] SET MESSAGE_TEXT = 'Stock cannot be negative'; END IF; END;

Drag options to blanks, or click blank then click option'

A<

B0

C'45000'

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<=' instead of '<' causing incorrect logic.

Using wrong SQLSTATE code.

Not comparing to zero.

The trigger checks if the new stock is less than zero (< 0) and raises an error with SQLSTATE '45000' if true.

Practice

1. Why are triggers needed in a database?
Triggers are special procedures that run automatically. What is their main purpose?

easy

A. To write complex queries for reports

B. To manually update data in tables

C. To create new tables in the database

D. To automatically perform actions when data changes happen

Solution

1. Step 1: Understand what triggers do

   Triggers run automatically when data changes happen in a table, like insert, update, or delete.

2. Step 2: Identify the main purpose

   The main purpose is to automate actions without manual intervention, such as enforcing rules or logging changes.

3. Final Answer:

   To automatically perform actions when data changes happen -> Option D

4. Quick Check:

   Triggers run automatically = D [OK]

Hint: Triggers act automatically on data changes [OK]

Common Mistakes:

• Thinking triggers are for manual updates
• Confusing triggers with creating tables
• Assuming triggers write reports

2. Which of the following is the correct syntax to create a trigger that runs after an INSERT on a table named Employees?

easy

A. CREATE TRIGGER trg AFTER INSERT ON Employees BEGIN ... END;

B. CREATE TRIGGER trg BEFORE UPDATE ON Employees BEGIN ... END;

C. CREATE TRIGGER trg ON Employees AFTER DELETE BEGIN ... END;

D. CREATE TRIGGER trg INSERT AFTER Employees BEGIN ... END;

Solution

1. Step 1: Identify correct trigger timing and event

   The question asks for a trigger after INSERT on Employees, so syntax must say AFTER INSERT ON Employees.

2. Step 2: Check syntax structure

   CREATE TRIGGER trg AFTER INSERT ON Employees BEGIN ... END; matches the correct order: CREATE TRIGGER name AFTER INSERT ON table BEGIN ... END;

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON Employees BEGIN ... END; -> Option A

4. Quick Check:

   Correct trigger syntax = C [OK]

Hint: Syntax: CREATE TRIGGER name AFTER|BEFORE event ON table [OK]

Common Mistakes:

• Mixing BEFORE and AFTER keywords
• Placing table name in wrong position
• Using ON before event instead of after

3. Consider this trigger on table Orders:

CREATE TRIGGER trg_log AFTER UPDATE ON Orders BEGIN INSERT INTO AuditLog VALUES('Order updated'); END;

What happens when an order is updated?

medium

A. Nothing happens automatically

B. A new row with 'Order updated' is added to AuditLog

C. The Orders table is deleted

D. The update on Orders is canceled

Solution

1. Step 1: Understand trigger action on UPDATE

   The trigger runs AFTER UPDATE on Orders and inserts a row into AuditLog with text 'Order updated'.

2. Step 2: Identify the effect of the trigger

   Each time an order is updated, a new row is added to AuditLog recording the event.

3. Final Answer:

   A new row with 'Order updated' is added to AuditLog -> Option B

4. Quick Check:

   Trigger inserts log row = B [OK]

Hint: Trigger inserts log row after update [OK]

Common Mistakes:

• Thinking trigger cancels update
• Assuming no action happens automatically
• Confusing trigger with delete operation

4. You wrote this trigger:

CREATE TRIGGER trg_check BEFORE INSERT ON Users BEGIN IF NEW.age < 18 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Age must be 18 or older'; END IF; END;

But it does not prevent inserting users younger than 18. What is the likely error?

medium

A. The trigger does not have a proper delimiter for multiple statements

B. The SIGNAL statement syntax is incorrect

C. The trigger should be AFTER INSERT, not BEFORE

D. Triggers cannot check conditions on NEW data

Solution

1. Step 1: Analyze trigger structure with multiple statements

   The trigger has IF and SIGNAL inside BEGIN...END, which requires proper statement delimiters (like semicolons) to separate commands.

2. Step 2: Identify missing delimiter issue

   Without delimiters, the database cannot parse the trigger body correctly, so the condition check fails.

3. Final Answer:

   The trigger does not have a proper delimiter for multiple statements -> Option A

4. Quick Check:

   Missing delimiters cause trigger failure = A [OK]

Hint: Use delimiters for multiple statements in triggers [OK]

Common Mistakes:

• Changing BEFORE to AFTER incorrectly
• Assuming SIGNAL syntax is wrong without checking
• Believing triggers can't access NEW data

5. A company wants to ensure that whenever a product's price is updated, the old price is saved in a PriceHistory table automatically. Which trigger design best achieves this?

hard

A. A BEFORE DELETE trigger on Products that inserts old price into PriceHistory

B. An AFTER INSERT trigger on Products that inserts new price into PriceHistory

C. An AFTER UPDATE trigger on Products that inserts old price into PriceHistory

D. A BEFORE UPDATE trigger on Products that inserts old price into PriceHistory

Solution

1. Step 1: Understand when old price is available

   The old price is available after the update operation starts but before the new data replaces it, so a trigger on UPDATE is needed.

2. Step 2: Choose trigger timing to save old price

   Using AFTER UPDATE ensures the update succeeded, and the trigger can insert the old price (accessible via OLD) into PriceHistory.

3. Final Answer:

   An AFTER UPDATE trigger on Products that inserts old price into PriceHistory -> Option C

4. Quick Check:

   Save old price after update = A [OK]

Hint: Use AFTER UPDATE trigger to save old data changes [OK]

Common Mistakes:

• Using BEFORE DELETE instead of UPDATE
• Using AFTER INSERT which doesn't track price changes
• Choosing BEFORE UPDATE which may block or cause errors