Trigger execution order in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Order of BEFORE triggers on INSERT

Consider a table with two BEFORE INSERT triggers named trigger_a and trigger_b. If both triggers are set to fire BEFORE INSERT, in what order will they execute by default?

PostgreSQL

CREATE TABLE test_order(id SERIAL PRIMARY KEY, val TEXT);

CREATE FUNCTION trigger_a_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Trigger A fired';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION trigger_b_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'Trigger B fired';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_a BEFORE INSERT ON test_order FOR EACH ROW EXECUTE FUNCTION trigger_a_func();
CREATE TRIGGER trigger_b BEFORE INSERT ON test_order FOR EACH ROW EXECUTE FUNCTION trigger_b_func();

INSERT INTO test_order(val) VALUES ('test');

A trigger_a fires first, then trigger_b

B trigger_b fires first, then trigger_a

C Both triggers fire simultaneously

D The order is random and cannot be predicted

💡 Hint

PostgreSQL executes triggers in alphabetical order by trigger name when no explicit ordering is set.

PostgreSQL executes multiple triggers of the same type on the same event in alphabetical order by trigger name. Since 'trigger_a' comes before 'trigger_b', it fires first.

❓ query_result

intermediate

Execution order of AFTER triggers on UPDATE

A table has two AFTER UPDATE triggers: update_first and update_second. Both are row-level triggers. Which trigger executes first after an UPDATE statement?

PostgreSQL

CREATE TABLE update_test(id SERIAL PRIMARY KEY, data TEXT);
INSERT INTO update_test(data) VALUES ('initial');

CREATE FUNCTION update_first_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'update_first fired';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION update_second_func() RETURNS trigger AS $$
BEGIN
  RAISE NOTICE 'update_second fired';
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_first AFTER UPDATE ON update_test FOR EACH ROW EXECUTE FUNCTION update_first_func();
CREATE TRIGGER update_second AFTER UPDATE ON update_test FOR EACH ROW EXECUTE FUNCTION update_second_func();

UPDATE update_test SET data = 'changed' WHERE id = 1;

A update_second fires before update_first

B update_first fires before update_second

C Both triggers fire simultaneously

D The order depends on the order of UPDATE statement execution

💡 Hint

Trigger execution order is alphabetical by trigger name if no other ordering is specified.

PostgreSQL executes AFTER triggers in alphabetical order by trigger name. Since 'update_first' comes before 'update_second', it fires first.

🧠 Conceptual

advanced

Effect of trigger firing order on data modification

If two BEFORE UPDATE triggers modify the same column in a row, how does the firing order affect the final value stored in that column?

A The last trigger to fire determines the final column value

B The first trigger to fire determines the final column value

C Both triggers' changes are merged automatically

D The database throws an error due to conflict

💡 Hint

Each trigger modifies the row data passed to the next trigger.

In BEFORE triggers, the row data is passed along the chain. Each trigger can modify the row, and the last trigger's changes are what get stored.

📝 Syntax

advanced

Correct syntax to specify trigger firing order

Which of the following statements correctly sets the firing order of two triggers so that trigger_b fires before trigger_a on INSERT?

A CREATE TRIGGER trigger_b BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_b(); CREATE TRIGGER trigger_a BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_a();

B ALTER TRIGGER trigger_b ON table_name ORDER BEFORE trigger_a;

C PostgreSQL does not support explicit trigger firing order; order depends on trigger name alphabetical order.

D CREATE TRIGGER trigger_b BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_b() WHEN (TRUE); CREATE TRIGGER trigger_a BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION func_a();

💡 Hint

Check PostgreSQL documentation on trigger ordering capabilities.

PostgreSQL does not provide syntax to explicitly set trigger firing order. The order is determined by trigger name alphabetical order.

🔧 Debug

expert

Diagnosing unexpected trigger execution order

A developer created two BEFORE INSERT triggers named 'a_trigger' and 'b_trigger'. They expect 'b_trigger' to fire first, but 'a_trigger' fires first instead. What is the most likely cause?

A Triggers always fire in creation order, so the first created fires first

B The database is misconfigured and triggers are firing incorrectly

C The trigger firing order is random and cannot be controlled

D 'a_trigger' comes before 'b_trigger' alphabetically, so it fires first

💡 Hint

Consider how PostgreSQL determines trigger firing order by default.

PostgreSQL executes multiple triggers of the same type on the same event in alphabetical order by trigger name. Since 'a_trigger' comes before 'b_trigger', it fires first.