Why triggers are needed in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a trigger that automatically updates a timestamp column when a row is modified.

PostgreSQL

CREATE TRIGGER update_timestamp BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION [1]();

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

Aupdate_timestamp_function

Binsert_timestamp_function

Cdelete_timestamp_function

Dlog_changes_function

Attempts:

3 left

💡 Hint

Common Mistakes

Using a function meant for insert or delete operations.

Not specifying the correct function name.

The trigger calls the function update_timestamp_function to update the timestamp before any update on the users table.

2fill in blank

medium

Complete the code to create a trigger that logs changes after a row is deleted.

PostgreSQL

CREATE TRIGGER log_delete AFTER DELETE ON orders FOR EACH ROW EXECUTE FUNCTION [1]();

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

Alog_insert_function

Bupdate_log_function

Clog_delete_function

Daudit_function

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a function that logs inserts instead of deletes.

Using a generic audit function without proper implementation.

The trigger uses log_delete_function to log details after a row is deleted from the orders table.

3fill in blank

hard

Fix the error in the trigger creation code by selecting the correct timing keyword.

PostgreSQL

CREATE TRIGGER check_stock [1] INSERT ON products FOR EACH ROW EXECUTE FUNCTION check_stock_function();

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

ABEFORE

BDURING

CAFTER

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'DURING' which is not a valid keyword.

Using 'ON' which is part of syntax but not timing.

The correct timing for this trigger is BEFORE INSERT to check stock before adding a new product.

4fill in blank

hard

Fill both blanks to create a trigger that prevents deletion if stock is low.

PostgreSQL

CREATE TRIGGER prevent_low_stock [1] DELETE ON inventory FOR EACH ROW WHEN (OLD.quantity [2] 5) EXECUTE FUNCTION prevent_delete_function();

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

ABEFORE

B>

C<=

DAFTER

Attempts:

3 left

💡 Hint

Common Mistakes

Using AFTER which is too late to prevent deletion.

Using > instead of <= in the condition.

The trigger runs BEFORE DELETE and checks if the old quantity is less than or equal to 5 to prevent deletion.

5fill in blank

hard

Fill all three blanks to create a trigger that updates a log table after an update on sales.

PostgreSQL

CREATE TRIGGER [1] [2] UPDATE ON sales FOR EACH ROW EXECUTE FUNCTION [3]();

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

Alog_sales_update

BAFTER

Cupdate_sales_log_function

DBEFORE

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEFORE instead of AFTER for logging.

Mismatching trigger name and function name.

The trigger named log_sales_update runs AFTER an UPDATE on sales and calls update_sales_log_function to update the log table.