0
0
PostgreSQLquery~20 mins

Why triggers are needed in PostgreSQL - Challenge Your Understanding

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Challenge - 5 Problems
🎖️
Trigger Mastery
Get all challenges correct to earn this badge!
Test your skills under time pressure!
🧠 Conceptual
intermediate
2:00remaining
Purpose of Triggers in Databases

Why do we use triggers in a database system like PostgreSQL?

ATo manually update data by running queries every time a change is needed.
BTo automatically execute a set of actions when certain changes happen in the database.
CTo store large files like images and videos inside the database.
DTo create backup copies of the entire database automatically every hour.
Attempts:
2 left
💡 Hint

Think about how to make the database respond by itself when data changes.

🧠 Conceptual
intermediate
2:00remaining
When to Use Triggers

Which situation best shows when a trigger is useful?

ATo change the database password regularly for security.
BTo write a report manually every month about sales data.
CTo send an email notification automatically when a new user registers.
DTo create a new database user with specific permissions.
Attempts:
2 left
💡 Hint

Triggers help automate tasks that happen right after data changes.

❓ query_result
advanced
3:00remaining
Effect of a Trigger on Insert

Given a trigger that updates a 'last_modified' timestamp column whenever a row is inserted or updated, what will be the value of 'last_modified' after inserting a new row?

PostgreSQL
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, last_modified TIMESTAMP);

CREATE OR REPLACE FUNCTION update_last_modified() RETURNS TRIGGER AS $$
BEGIN
  NEW.last_modified = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_last_modified
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION update_last_modified();

INSERT INTO products (name) VALUES ('Book');

SELECT last_modified FROM products WHERE name = 'Book';
AThe current timestamp at the moment of insertion.
BNULL because the column was not set explicitly.
CThe timestamp of the previous update to the table.
DAn error occurs because the trigger tries to modify NEW.
Attempts:
2 left
💡 Hint

The trigger sets the 'last_modified' field to the current time before inserting.

🔧 Debug
advanced
3:00remaining
Why a Trigger Does Not Fire

A developer created a trigger to log deletions on a table, but the trigger never runs. What is the most likely reason?

PostgreSQL
CREATE OR REPLACE FUNCTION log_delete() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO deletions_log(table_name, deleted_id, deleted_at) VALUES ('users', OLD.id, NOW());
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_log_delete
AFTER DELETE ON users
FOR EACH ROW EXECUTE FUNCTION log_delete();

-- Then they run: DELETE FROM users WHERE id = 10;
AThe deletions_log table does not exist, so the trigger fails silently.
BThe trigger is not enabled by default and must be activated manually.
CThe trigger function returns OLD, but it should return NEW.
DThe trigger is AFTER DELETE, so it cannot access OLD values.
Attempts:
2 left
💡 Hint

Check if all referenced tables exist and the trigger function runs without errors.

❓ optimization
expert
4:00remaining
Optimizing Trigger Performance

You have a trigger that runs a complex calculation on every row update in a large table. What is the best way to improve performance without losing the trigger's benefits?

AAdd an index on the table to speed up the trigger function.
BRemove the trigger and run the calculation manually once a day.
CChange the trigger to run AFTER the update instead of BEFORE.
DModify the trigger to run only when specific columns change, not on every update.
Attempts:
2 left
💡 Hint

Think about reducing unnecessary trigger executions.