AFTER trigger behavior in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of AFTER INSERT trigger on a table

Consider a PostgreSQL table orders with an AFTER INSERT trigger that inserts a record into order_log table. What will be the content of order_log after inserting one row into orders?

PostgreSQL

CREATE TABLE orders (id SERIAL PRIMARY KEY, product TEXT);
CREATE TABLE order_log (order_id INT, action TEXT);

CREATE OR REPLACE FUNCTION log_order_insert() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO order_log(order_id, action) VALUES (NEW.id, 'inserted');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION log_order_insert();

INSERT INTO orders(product) VALUES ('Book');

SELECT * FROM order_log;

A [{"order_id": 1, "action": "inserted"}]

B []

C SyntaxError

D [{"order_id": null, "action": "inserted"}]

💡 Hint

AFTER triggers run after the row is inserted, so NEW.id is available.

The AFTER INSERT trigger runs after the row is inserted, so the NEW record has the assigned id. The trigger inserts a log record with that id and action 'inserted'.

🧠 Conceptual

intermediate

When does an AFTER trigger execute in PostgreSQL?

Choose the correct statement about when an AFTER trigger executes in PostgreSQL.

A AFTER triggers execute only if the statement fails.

B AFTER triggers execute before the row is modified.

C AFTER triggers execute before the BEFORE triggers.

D AFTER triggers execute after the data modification statement completes but after the row is modified.

💡 Hint

Think about the order of BEFORE and AFTER triggers relative to the data change.

AFTER triggers run after the row is modified and before the statement completes, allowing actions that depend on the final data state.

📝 Syntax

advanced

Identify the syntax error in AFTER trigger creation

Which option contains a syntax error when creating an AFTER UPDATE trigger in PostgreSQL?

PostgreSQL

CREATE OR REPLACE FUNCTION update_log() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log(table_name, action) VALUES ('users', 'updated');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

A CREATE TRIGGER after_update_users AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_log();

B CREATE TRIGGER after_update_users AFTER UPDATE ON users EXECUTE FUNCTION update_log();

C CREATE TRIGGER after_update_users AFTER UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_log();

D ;)(gol_etadpu NOITCNUF ETUCEXE WOR HCAE ROF sresu NO ETADPU RETFA sresu_etadpu_retfa REGGIRT ETAERC

💡 Hint

Check if the FOR EACH ROW clause is mandatory for row-level triggers.

Option B is missing the FOR EACH ROW clause, which is required for row-level triggers in PostgreSQL.

❓ optimization

advanced

Optimizing AFTER DELETE trigger to avoid performance issues

You have an AFTER DELETE trigger that logs deleted rows into a log table. Which approach optimizes performance best?

A Use a statement-level AFTER DELETE trigger instead of a row-level trigger.

B Use a BEFORE DELETE trigger to log rows before deletion.

C Use a row-level AFTER DELETE trigger with complex queries inside.

D Disable triggers during bulk deletes to speed up operations.

💡 Hint

Consider how many times the trigger fires and the cost per row.

Statement-level triggers fire once per statement, reducing overhead compared to row-level triggers that fire per row.

🔧 Debug

expert

Why does an AFTER INSERT trigger not see the inserted row in a concurrent transaction?

You created an AFTER INSERT trigger that queries the same table to count rows. Sometimes, the count does not include the newly inserted row. Why?

A The trigger function is missing a COMMIT statement to finalize the insert.

B AFTER triggers run before the row is inserted, so the row is not visible yet.

C The trigger runs in a different transaction and cannot see uncommitted data.

D The table is locked exclusively, blocking the trigger's query.

💡 Hint

Think about transaction isolation and visibility of data within triggers.

AFTER triggers run within the same transaction, but if the trigger queries the table in a way that does not see uncommitted changes (due to isolation levels), it may not see the new row yet.

Practice

1. What is the main purpose of an AFTER trigger in PostgreSQL?

easy

A. To execute a function after the main database operation completes successfully

B. To prevent a database operation from happening

C. To execute a function before the database operation starts

D. To rollback the transaction if an error occurs

Solution

1. Step 1: Understand the timing of AFTER triggers

   AFTER triggers run only after the main database action (INSERT, UPDATE, DELETE) has completed successfully.

2. Step 2: Identify the purpose of AFTER triggers

   They are used to perform actions like logging or notifications after the main operation finishes.

3. Final Answer:

   To execute a function after the main database operation completes successfully -> Option A

4. Quick Check:

   AFTER trigger = runs after operation [OK]

Hint: AFTER triggers run only after successful main actions [OK]

Common Mistakes:

• Confusing AFTER with BEFORE triggers
• Thinking AFTER triggers can stop the main operation
• Assuming AFTER triggers run before the operation

2. Which of the following is the correct syntax to create an AFTER INSERT trigger in PostgreSQL?

easy

A. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name();

B. CREATE TRIGGER trg AFTER INSERT ON table_name EXECUTE FUNCTION func_name();

C. CREATE TRIGGER trg BEFORE INSERT ON table_name EXECUTE FUNCTION func_name();

D. CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH STATEMENT EXECUTE FUNCTION func_name();

Solution

1. Step 1: Recall the correct CREATE TRIGGER syntax

   PostgreSQL requires specifying FOR EACH ROW or FOR EACH STATEMENT for triggers.

2. Step 2: Identify the correct syntax for AFTER INSERT

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name(); correctly uses AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name();

3. Final Answer:

   CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name(); -> Option A

4. Quick Check:

   AFTER INSERT + FOR EACH ROW + EXECUTE FUNCTION = CREATE TRIGGER trg AFTER INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_name(); [OK]

Hint: AFTER triggers need FOR EACH ROW or STATEMENT [OK]

Common Mistakes:

• Omitting FOR EACH ROW or FOR EACH STATEMENT
• Using BEFORE instead of AFTER
• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION in modern PostgreSQL

3. Given the following trigger and table, what will be the output after inserting a row?

CREATE TABLE users(id SERIAL PRIMARY KEY, name TEXT);
CREATE FUNCTION log_insert() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Inserted user: %', NEW.name;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER after_user_insert AFTER INSERT ON users FOR EACH ROW EXECUTE FUNCTION log_insert();

INSERT INTO users(name) VALUES ('Alice');

medium

A. No output, the insert happens silently

B. An error occurs because RETURN NEW is invalid in AFTER trigger

C. The insert is rolled back due to the trigger

D. A notice message: 'Inserted user: Alice'

Solution

1. Step 1: Understand the trigger function behavior

   The function raises a NOTICE with the inserted user's name after insert.

2. Step 2: Recognize AFTER trigger effects

   AFTER triggers run after the insert, so the notice will be shown, and the insert completes successfully.

3. Final Answer:

   A notice message: 'Inserted user: Alice' -> Option D

4. Quick Check:

   AFTER trigger raises notice = A notice message: 'Inserted user: Alice' [OK]

Hint: AFTER triggers can raise notices after insert [OK]

Common Mistakes:

• Thinking RETURN NEW is invalid in AFTER triggers
• Expecting no output from the trigger
• Assuming the insert is rolled back

4. You created an AFTER UPDATE trigger but it never fires. Which of the following is the most likely cause?

medium

A. The trigger was created as BEFORE UPDATE instead of AFTER UPDATE

B. The UPDATE statement does not change any column values

C. The trigger function does not return NEW or OLD

D. The trigger is defined FOR EACH STATEMENT but the function expects FOR EACH ROW

Solution

1. Step 1: Understand when AFTER UPDATE triggers fire

   AFTER UPDATE triggers fire only if the UPDATE actually changes data.

2. Step 2: Analyze the cause of no trigger firing

   If the UPDATE sets columns to their existing values, no actual change occurs, so the trigger does not fire.

3. Final Answer:

   The UPDATE statement does not change any column values -> Option B

4. Quick Check:

   UPDATE with no change = no AFTER trigger fire [OK]

Hint: AFTER UPDATE triggers fire only on actual data changes [OK]

Common Mistakes:

• Assuming triggers fire even if no data changes
• Confusing BEFORE and AFTER triggers
• Not matching trigger function with trigger type

5. You want to log every DELETE on a table after it happens, but only if the deleted row's status is 'active'. Which is the best way to implement this using an AFTER trigger?

hard

A. Create an AFTER DELETE trigger that logs all deletes without checking status

B. Create a BEFORE DELETE trigger that checks OLD.status and prevents deletion if not 'active'

C. Create an AFTER DELETE trigger that checks if OLD.status = 'active' inside the trigger function before logging

D. Create a BEFORE DELETE trigger that logs only if OLD.status = 'active'

Solution

1. Step 1: Understand the requirement for conditional logging after delete

   We want to log only after the delete happens and only for rows with status 'active'.

2. Step 2: Choose the correct trigger timing and condition

   AFTER DELETE trigger can access OLD row data and conditionally log if OLD.status = 'active'.

3. Final Answer:

   Create an AFTER DELETE trigger that checks if OLD.status = 'active' inside the trigger function before logging -> Option C

4. Quick Check:

   Conditional logging after delete = AFTER trigger with check [OK]

Hint: Use AFTER DELETE trigger with condition inside function [OK]

Common Mistakes:

• Using BEFORE trigger which runs before deletion
• Logging all deletes without condition
• Trying to prevent deletion instead of logging