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.

Practice

1. Why are triggers needed in a PostgreSQL database?

easy

A. To automatically perform actions when data changes

B. To manually update data by user commands

C. To store large files outside the database

D. To create user accounts and manage permissions

Solution

1. Step 1: Understand the purpose of triggers

   Triggers run automatically when data changes, so they help automate tasks without manual intervention.

2. Step 2: Compare options to trigger function

   Options A, C, and D describe manual updates, file storage, and user management, which are not the main purpose of triggers.

3. Final Answer:

   To automatically perform actions when data changes -> Option A

4. Quick Check:

   Triggers automate tasks = To automatically perform actions when data changes [OK]

Hint: Triggers run automatically on data changes [OK]

Common Mistakes:

• Thinking triggers are for manual updates
• Confusing triggers with file storage
• Assuming triggers manage user permissions

2. Which of the following is the correct syntax to create a trigger in PostgreSQL?

easy

A. CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name();

B. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name();

C. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name();

D. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name();

Solution

1. Step 1: Review PostgreSQL trigger syntax

   The correct syntax uses CREATE TRIGGER, timing (BEFORE/AFTER), event (INSERT/UPDATE/DELETE), table, FOR EACH ROW or STATEMENT, and EXECUTE FUNCTION.

2. Step 2: Check each option for syntax correctness

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); matches the correct syntax exactly. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name(); uses CALL instead of EXECUTE FUNCTION, which is invalid. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name(); uses EXECUTE PROCEDURE, deprecated in modern PostgreSQL. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name(); places ON table_name before timing and event, which is incorrect order.

3. Final Answer:

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); -> Option A

4. Quick Check:

   Correct trigger syntax = CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); [OK]

Hint: Use EXECUTE FUNCTION, not CALL or PROCEDURE [OK]

Common Mistakes:

• Using CALL instead of EXECUTE FUNCTION
• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION
• Placing ON table_name in wrong position

3. Given this trigger function and trigger, what will happen when a new row is inserted into orders table?

CREATE FUNCTION check_stock() RETURNS trigger AS $$ BEGIN IF NEW.quantity > stock THEN RAISE EXCEPTION 'Not enough stock'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER stock_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_stock();

medium

A. The trigger will delete the row if quantity is too high

B. The insert will fail if quantity is greater than stock

C. The trigger will update the stock automatically

D. The insert will always succeed regardless of quantity

Solution

1. Step 1: Analyze the trigger function logic

   The function checks if NEW.quantity is greater than stock. If yes, it raises an exception to stop the insert.

2. Step 2: Understand trigger timing and effect

   The trigger runs BEFORE INSERT on orders. If exception is raised, insert fails. Otherwise, it returns NEW row to proceed.

3. Final Answer:

   The insert will fail if quantity is greater than stock -> Option B

4. Quick Check:

   Exception raised on high quantity = The insert will fail if quantity is greater than stock [OK]

Hint: Exception stops insert if condition met [OK]

Common Mistakes:

• Assuming trigger updates stock automatically
• Thinking insert always succeeds
• Believing trigger deletes rows

4. You created this trigger but it does not run when you insert data:

CREATE TRIGGER log_insert AFTER INSERT ON sales EXECUTE FUNCTION log_sale();

What is the likely problem?

medium

A. AFTER INSERT triggers cannot be created

B. Trigger function name is incorrect

C. Missing FOR EACH ROW clause in trigger definition

D. log_sale() function must return void

Solution

1. Step 1: Check trigger syntax requirements

   PostgreSQL requires FOR EACH ROW or FOR EACH STATEMENT clause in CREATE TRIGGER statement.

2. Step 2: Identify missing clause in given trigger

   The trigger lacks FOR EACH ROW or FOR EACH STATEMENT, so it is invalid and will not run.

3. Final Answer:

   Missing FOR EACH ROW clause in trigger definition -> Option C

4. Quick Check:

   Triggers need FOR EACH ROW/STATEMENT clause [OK]

Hint: Always include FOR EACH ROW or STATEMENT [OK]

Common Mistakes:

• Assuming AFTER INSERT triggers are disallowed
• Thinking function name causes trigger failure
• Believing trigger functions must return void

5. You want to keep a history of changes to the employees table automatically. Which trigger setup best achieves this?

hard

A. Create AFTER UPDATE trigger that drops employees table

B. Create AFTER DELETE trigger that updates employees table

C. Create BEFORE INSERT trigger that deletes old rows

D. Create BEFORE UPDATE trigger that inserts old row into history table

Solution

1. Step 1: Understand requirement to keep change history

   To keep history, old data must be saved before it changes or is deleted.

2. Step 2: Evaluate trigger options for history tracking

   Create BEFORE UPDATE trigger that inserts old row into history table uses BEFORE UPDATE trigger to save old row to history table, which is correct. Create AFTER DELETE trigger that updates employees table updates employees after delete, which is unrelated. Create BEFORE INSERT trigger that deletes old rows deletes old rows before insert, losing data. Create AFTER UPDATE trigger that drops employees table drops the table, which is destructive.

3. Final Answer:

   Create BEFORE UPDATE trigger that inserts old row into history table -> Option D

4. Quick Check:

   Save old data before update = Create BEFORE UPDATE trigger that inserts old row into history table [OK]

Hint: Save old row before update for history [OK]

Common Mistakes:

• Using AFTER DELETE to update main table
• Deleting old rows on insert
• Dropping table by mistake