Lecture 14 - Triggers

Question 1

PostgreSQL Triggers - Definition

Answer 1

A PostgreSQL trigger is a function automatically invoked when specific events (INSERT, UPDATE, DELETE, or TRUNCATE) occur on a table.

Question 2

PosgreSQL - Purpose

Answer 2

Triggers ensure automatic execution of predefined actions when data in a table changes, providing consistency and automation within the database.

Question 3

Types of PostgreSQL Triggers

Answer 3

1. Row-Level Triggers:
   
   • Triggered once for each row affected by the event.
   • Example: If 20 rows are updated, the row-level trigger is invoked 20 times.
2. Statement-Level Triggers:
   
   • Triggered once per event, regardless of how many rows are affected.
   • Example: If 20 rows are updated, the statement-level trigger is invoked only once.
3. Timing:
   
   • BEFORE Trigger: Invoked before the event occurs, allowing modification or skipping of the operation on the current row.
   • AFTER Trigger: Invoked after the event, with all changes made available to the trigger.

Question 4

When to use triggers

Answer 4

• Cross-Functionality: Useful when multiple applications access the database, ensuring certain actions occur automatically on data modification.
• Complex Data Integrity: Helps maintain data integrity rules that are challenging to enforce outside the database level.
• Examples: Automatically keeping a history of data changes or maintaining related records across multiple tables when new data is inserted.

Question 5

PostgreSQL Triggers bs SQL Triggers

Answer 5

• TRUNCATE Event: PostgreSQL triggers can be fired for the TRUNCATE event, unlike some other SQL databases.
• Statement-Level Triggers on Views: PostgreSQL allows statement-level triggers to be defined on views.
• User-Defined Functions: PostgreSQL requires a user-defined function as the trigger action, whereas the SQL standard allows direct SQL commands.