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.