Why triggers are needed in PostgreSQL - Quick Recap

Recall & Review

beginner

What is a trigger in a database?

A trigger is a special procedure that automatically runs when certain events happen in the database, like inserting, updating, or deleting data.

beginner

Why do we need triggers in a database?

Triggers help keep data correct and consistent by automatically checking or changing data when something happens, without needing a person to do it manually.

intermediate

How do triggers help with data integrity?

Triggers can stop wrong data from being saved or fix data right away, so the database always stays accurate and reliable.

beginner

Give an example of when a trigger is useful.

For example, a trigger can automatically update a 'last_modified' date whenever a record changes, so you always know when data was last updated.

intermediate

Can triggers help automate tasks in the database?

Yes, triggers can run tasks automatically like sending alerts, updating related tables, or logging changes without extra work from users.

What event can cause a trigger to run in PostgreSQL?

A Opening a database connection

B Running a SELECT query

C Inserting data into a table

D Closing the database

Triggers run automatically when data changes, like inserting, updating, or deleting rows.

Why are triggers useful for data integrity?

A They automatically check or fix data when it changes

B They speed up database backups

C They create new tables

D They delete old data automatically

Triggers help keep data correct by running checks or updates when data changes.

Which of these is a common use of triggers?

A Creating user accounts

B Automatically updating a timestamp when data changes

C Backing up the database daily

D Changing the database password

Triggers often update fields like 'last_modified' automatically when data is changed.

Can triggers run without user action after setup?

A Only if the database is restarted

B No, users must run them manually

C Only during database backups

D Yes, they run automatically on defined events

Triggers run automatically when their event happens, no manual action needed.

Which of these is NOT a reason to use triggers?

A To improve network speed

B To enforce business rules

C To automate repetitive tasks

D To maintain data consistency

Triggers do not affect network speed; they help automate tasks and keep data consistent.

Explain why triggers are important in maintaining data integrity in a database.

Think about how triggers act like automatic helpers that watch data changes.

Describe a real-life example where a trigger could automate a task in a database.

Consider tasks that happen every time data changes without needing a person to do them.

Practice

1. Why are triggers needed in a PostgreSQL database?

easy

A. To automatically perform actions when data changes

B. To manually update data by user commands

C. To store large files outside the database

D. To create user accounts and manage permissions

Solution

1. Step 1: Understand the purpose of triggers

   Triggers run automatically when data changes, so they help automate tasks without manual intervention.

2. Step 2: Compare options to trigger function

   Options A, C, and D describe manual updates, file storage, and user management, which are not the main purpose of triggers.

3. Final Answer:

   To automatically perform actions when data changes -> Option A

4. Quick Check:

   Triggers automate tasks = To automatically perform actions when data changes [OK]

Hint: Triggers run automatically on data changes [OK]

Common Mistakes:

• Thinking triggers are for manual updates
• Confusing triggers with file storage
• Assuming triggers manage user permissions

2. Which of the following is the correct syntax to create a trigger in PostgreSQL?

easy

A. CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name();

B. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name();

C. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name();

D. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name();

Solution

1. Step 1: Review PostgreSQL trigger syntax

   The correct syntax uses CREATE TRIGGER, timing (BEFORE/AFTER), event (INSERT/UPDATE/DELETE), table, FOR EACH ROW or STATEMENT, and EXECUTE FUNCTION.

2. Step 2: Check each option for syntax correctness

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); matches the correct syntax exactly. CREATE TRIGGER trigger_name AFTER UPDATE ON table_name CALL function_name(); uses CALL instead of EXECUTE FUNCTION, which is invalid. CREATE TRIGGER trigger_name ON table_name BEFORE DELETE EXECUTE PROCEDURE function_name(); uses EXECUTE PROCEDURE, deprecated in modern PostgreSQL. CREATE TRIGGER trigger_name ON table_name FOR EACH STATEMENT EXECUTE FUNCTION function_name(); places ON table_name before timing and event, which is incorrect order.

3. Final Answer:

   CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); -> Option A

4. Quick Check:

   Correct trigger syntax = CREATE TRIGGER trigger_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE FUNCTION function_name(); [OK]

Hint: Use EXECUTE FUNCTION, not CALL or PROCEDURE [OK]

Common Mistakes:

• Using CALL instead of EXECUTE FUNCTION
• Using EXECUTE PROCEDURE instead of EXECUTE FUNCTION
• Placing ON table_name in wrong position

3. Given this trigger function and trigger, what will happen when a new row is inserted into orders table?

CREATE FUNCTION check_stock() RETURNS trigger AS $$ BEGIN IF NEW.quantity > stock THEN RAISE EXCEPTION 'Not enough stock'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER stock_check BEFORE INSERT ON orders FOR EACH ROW EXECUTE FUNCTION check_stock();

medium

A. The trigger will delete the row if quantity is too high

B. The insert will fail if quantity is greater than stock

C. The trigger will update the stock automatically

D. The insert will always succeed regardless of quantity

Solution

1. Step 1: Analyze the trigger function logic

   The function checks if NEW.quantity is greater than stock. If yes, it raises an exception to stop the insert.

2. Step 2: Understand trigger timing and effect

   The trigger runs BEFORE INSERT on orders. If exception is raised, insert fails. Otherwise, it returns NEW row to proceed.

3. Final Answer:

   The insert will fail if quantity is greater than stock -> Option B

4. Quick Check:

   Exception raised on high quantity = The insert will fail if quantity is greater than stock [OK]

Hint: Exception stops insert if condition met [OK]

Common Mistakes:

• Assuming trigger updates stock automatically
• Thinking insert always succeeds
• Believing trigger deletes rows

4. You created this trigger but it does not run when you insert data:

CREATE TRIGGER log_insert AFTER INSERT ON sales EXECUTE FUNCTION log_sale();

What is the likely problem?

medium

A. AFTER INSERT triggers cannot be created

B. Trigger function name is incorrect

C. Missing FOR EACH ROW clause in trigger definition

D. log_sale() function must return void

Solution

1. Step 1: Check trigger syntax requirements

   PostgreSQL requires FOR EACH ROW or FOR EACH STATEMENT clause in CREATE TRIGGER statement.

2. Step 2: Identify missing clause in given trigger

   The trigger lacks FOR EACH ROW or FOR EACH STATEMENT, so it is invalid and will not run.

3. Final Answer:

   Missing FOR EACH ROW clause in trigger definition -> Option C

4. Quick Check:

   Triggers need FOR EACH ROW/STATEMENT clause [OK]

Hint: Always include FOR EACH ROW or STATEMENT [OK]

Common Mistakes:

• Assuming AFTER INSERT triggers are disallowed
• Thinking function name causes trigger failure
• Believing trigger functions must return void

5. You want to keep a history of changes to the employees table automatically. Which trigger setup best achieves this?

hard

A. Create AFTER UPDATE trigger that drops employees table

B. Create AFTER DELETE trigger that updates employees table

C. Create BEFORE INSERT trigger that deletes old rows

D. Create BEFORE UPDATE trigger that inserts old row into history table

Solution

1. Step 1: Understand requirement to keep change history

   To keep history, old data must be saved before it changes or is deleted.

2. Step 2: Evaluate trigger options for history tracking

   Create BEFORE UPDATE trigger that inserts old row into history table uses BEFORE UPDATE trigger to save old row to history table, which is correct. Create AFTER DELETE trigger that updates employees table updates employees after delete, which is unrelated. Create BEFORE INSERT trigger that deletes old rows deletes old rows before insert, losing data. Create AFTER UPDATE trigger that drops employees table drops the table, which is destructive.

3. Final Answer:

   Create BEFORE UPDATE trigger that inserts old row into history table -> Option D

4. Quick Check:

   Save old data before update = Create BEFORE UPDATE trigger that inserts old row into history table [OK]

Hint: Save old row before update for history [OK]

Common Mistakes:

• Using AFTER DELETE to update main table
• Deleting old rows on insert
• Dropping table by mistake