Row-level vs statement-level triggers in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a row-level trigger that fires for each row inserted.

PostgreSQL

CREATE TRIGGER trg_example
AFTER INSERT ON employees
FOR EACH [1]
EXECUTE FUNCTION log_employee_insert();

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

AEVENT

BSTATEMENT

CTABLE

DROW

Attempts:

3 left

💡 Hint

Common Mistakes

Using FOR EACH STATEMENT instead of FOR EACH ROW for row-level triggers.

The FOR EACH ROW clause makes the trigger fire once for every row affected.

2fill in blank

medium

Complete the code to create a statement-level trigger that fires once per statement.

PostgreSQL

CREATE TRIGGER trg_example
BEFORE UPDATE ON orders
FOR EACH [1]
EXECUTE FUNCTION check_order_status();

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

ASTATEMENT

BTRANSACTION

CORDER

DROW

Attempts:

3 left

💡 Hint

Common Mistakes

Confusing row-level and statement-level triggers.

The FOR EACH STATEMENT clause makes the trigger fire once per SQL statement.

3fill in blank

hard

Fix the error in the trigger creation by choosing the correct timing keyword.

PostgreSQL

CREATE TRIGGER trg_update
[1] UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

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

ADURING

BAFTER

CBEFORE

DON

Attempts:

3 left

💡 Hint

Common Mistakes

Using invalid timing keywords like DURING.

Triggers must use BEFORE or AFTER to specify timing. DURING is invalid.

4fill in blank

hard

Fill both blanks to create a trigger that fires once per statement before delete.

PostgreSQL

CREATE TRIGGER trg_cleanup
[1] DELETE ON sessions
FOR EACH [2]
EXECUTE FUNCTION cleanup_sessions();

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

ABEFORE

BROW

CSTATEMENT

DAFTER

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing row-level and statement-level keywords.

The trigger fires BEFORE the delete statement and once per STATEMENT.

5fill in blank

hard

Fill all three blanks to create a row-level trigger that fires after insert.

PostgreSQL

CREATE TRIGGER trg_audit
[1] [2] ON employees
FOR EACH [3]
EXECUTE FUNCTION audit_changes();

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

AAFTER

BINSERT

CROW

DBEFORE

Attempts:

3 left

💡 Hint

Common Mistakes

Using BEFORE instead of AFTER.

Confusing event types or trigger levels.

The trigger fires AFTER INSERT on each ROW.