Trigger function creation in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this trigger function execution?

Consider the following PostgreSQL trigger function and trigger on a table employees:

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 UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION update_last_modified();

If a row in employees is updated, what will be the value of last_modified column after the update?

A The <code>last_modified</code> column will remain unchanged from its previous value.

B The <code>last_modified</code> column will be set to NULL.

C The update will fail because the trigger function does not return OLD.

D The <code>last_modified</code> column will be set to the current timestamp at update time.

💡 Hint

Think about what the trigger function does before returning the new row.

The trigger function sets the last_modified column to the current timestamp using NOW() before the row is updated. It returns the modified NEW row, so the update applies this new timestamp.

📝 Syntax

intermediate

Identify the syntax error in this trigger function definition

Which option contains the correct syntax for creating a trigger function in PostgreSQL?

PostgreSQL

CREATE OR REPLACE FUNCTION trg_func()
RETURNS TRIGGER AS $$
BEGIN
  -- some logic
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

A CREATE FUNCTION trg_func() RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;

B CREATE OR REPLACE FUNCTION trg_func RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;

C CREATE OR REPLACE FUNCTION trg_func() RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END; $$ LANGUAGE plpgsql;

D CREATE OR REPLACE FUNCTION trg_func() RETURNS TRIGGER AS $$ BEGIN RETURN NEW; END $$ LANGUAGE plpgsql;

💡 Hint

Check for missing parentheses, semicolons, and correct keywords.

Option C correctly includes parentheses after the function name, the RETURNS clause, the semicolon after END, and the language declaration.

❓ optimization

advanced

Optimize this trigger function to avoid unnecessary updates

Given this trigger function that updates a last_modified timestamp on every update:

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

How can you optimize it to only update last_modified if some column other than last_modified actually changed?

A Add an IF statement comparing OLD and NEW rows for changes excluding last_modified, then update last_modified only if changed.

B Always update last_modified regardless of changes to ensure consistency.

C Remove the trigger function and update last_modified manually in application code.

D Use a BEFORE INSERT trigger instead of BEFORE UPDATE to update last_modified.

💡 Hint

Think about how to detect changes between OLD and NEW rows.

By comparing OLD and NEW rows for changes excluding last_modified, the trigger can avoid unnecessary updates and reduce write load.

🔧 Debug

advanced

Why does this trigger function cause an infinite loop?

Consider this trigger function and trigger on table orders:

CREATE OR REPLACE FUNCTION trg_update_total()
RETURNS TRIGGER AS $$
BEGIN
  NEW.total := NEW.quantity * NEW.price;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_total
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION trg_update_total();

Updating any row in orders causes an infinite loop. Why?

A The table <code>orders</code> does not have columns <code>quantity</code> or <code>price</code>.

B The trigger updates the row, which fires the trigger again, causing recursion without a stop condition.

C The trigger is defined as AFTER UPDATE instead of BEFORE UPDATE.

D The trigger function is missing a RETURN statement, causing an error.

💡 Hint

Think about what happens when a trigger modifies the row it is triggered on.

The trigger modifies the row during an update, which causes the update to fire the trigger again, creating an infinite loop of updates.

🧠 Conceptual

expert

Which statement about trigger functions in PostgreSQL is true?

Choose the correct statement about trigger functions in PostgreSQL.

A Trigger functions can be written in any language supported by PostgreSQL, but must return type TRIGGER.

B Trigger functions must always return the NEW row for BEFORE triggers and the OLD row for AFTER triggers.

C Trigger functions can only be used with BEFORE INSERT triggers, not UPDATE or DELETE.

D Trigger functions cannot access the OLD row data in BEFORE INSERT triggers.

💡 Hint

Consider the flexibility of languages and return types for trigger functions.

Trigger functions in PostgreSQL can be written in any supported language (like plpgsql, plpython, etc.) but must always return type TRIGGER.