Trigger execution order in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a BEFORE INSERT trigger on table 'orders'.

PostgreSQL

CREATE TRIGGER before_insert_order
BEFORE [1] ON orders
FOR EACH ROW EXECUTE FUNCTION check_order();

Drag options to blanks, or click blank then click option'

AINSERT

BDELETE

CUPDATE

DTRUNCATE

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing UPDATE or DELETE instead of INSERT.

Using TRUNCATE which is not supported for triggers.

The trigger must fire before an INSERT operation, so the correct event is INSERT.

2fill in blank

medium

Complete the code to specify the trigger timing as AFTER.

PostgreSQL

CREATE TRIGGER after_update_customer
[1] UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION log_update();

Drag options to blanks, or click blank then click option'

ADURING

BBEFORE

CAFTER

DINSTEAD OF

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEFORE when the trigger should run after.

Choosing INSTEAD OF which is for views, not tables.

The trigger should execute after the UPDATE operation, so the timing is AFTER.

3fill in blank

hard

Fix the error in the trigger creation by choosing the correct trigger event.

PostgreSQL

CREATE TRIGGER trg_delete_product
BEFORE [1] ON products
FOR EACH ROW EXECUTE FUNCTION archive_product();

Drag options to blanks, or click blank then click option'

AINSERT

BDELETE

CUPDATE

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT or UPDATE instead of DELETE.

Using SELECT which is not a valid trigger event.

The trigger is named 'trg_delete_product' and should fire before a DELETE operation, so the event must be DELETE.

4fill in blank

hard

Fill both blanks to create a trigger that fires BEFORE UPDATE or DELETE on the 'employees' table.

PostgreSQL

CREATE TRIGGER trg_before_change
BEFORE [1] OR [2] ON employees
FOR EACH ROW EXECUTE FUNCTION validate_change();

Drag options to blanks, or click blank then click option'

AUPDATE

BINSERT

CDELETE

DTRUNCATE

Attempts:

3 left

💡 Hint

Common Mistakes

Using INSERT or TRUNCATE instead of UPDATE or DELETE.

Mixing up the order of events.

The trigger fires before UPDATE or DELETE events, so the blanks must be UPDATE and DELETE.

5fill in blank

hard

Fill all three blanks to create a trigger that fires AFTER INSERT, UPDATE, or DELETE on 'sales'.

PostgreSQL

CREATE TRIGGER trg_after_change
AFTER [1] OR [2] OR [3] ON sales
FOR EACH ROW EXECUTE FUNCTION audit_changes();

Drag options to blanks, or click blank then click option'

AINSERT

BUPDATE

CDELETE

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Including SELECT which is invalid.

Missing one of the three events.

The trigger fires after INSERT, UPDATE, or DELETE events, so the blanks must be INSERT, UPDATE, and DELETE.