0
0
PostgreSQLquery~20 mins

AFTER trigger behavior in PostgreSQL - Practice Problems & Coding Challenges

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
AFTER Trigger Mastery
Get all challenges correct to earn this badge!
Test your skills under time pressure!
❓ query_result
intermediate
2:00remaining
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[]
CSyntaxError
D[{"order_id": null, "action": "inserted"}]
Attempts:
2 left
💡 Hint

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

🧠 Conceptual
intermediate
1:30remaining
When does an AFTER trigger execute in PostgreSQL?

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

AAFTER triggers execute only if the statement fails.
BAFTER triggers execute before the row is modified.
CAFTER triggers execute before the BEFORE triggers.
DAFTER triggers execute after the data modification statement completes but after the row is modified.
Attempts:
2 left
💡 Hint

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

📝 Syntax
advanced
2:00remaining
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;
ACREATE TRIGGER after_update_users AFTER UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_log();
BCREATE TRIGGER after_update_users AFTER UPDATE ON users EXECUTE FUNCTION update_log();
CCREATE 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
Attempts:
2 left
💡 Hint

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

❓ optimization
advanced
2:00remaining
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?

AUse a statement-level AFTER DELETE trigger instead of a row-level trigger.
BUse a BEFORE DELETE trigger to log rows before deletion.
CUse a row-level AFTER DELETE trigger with complex queries inside.
DDisable triggers during bulk deletes to speed up operations.
Attempts:
2 left
💡 Hint

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

🔧 Debug
expert
3:00remaining
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?

AThe trigger function is missing a COMMIT statement to finalize the insert.
BAFTER triggers run before the row is inserted, so the row is not visible yet.
CThe trigger runs in a different transaction and cannot see uncommitted data.
DThe table is locked exclusively, blocking the trigger's query.
Attempts:
2 left
💡 Hint

Think about transaction isolation and visibility of data within triggers.