Perform Complex View Updates with INSTEAD OF Trigger

Update Data through a View Using an INSTEAD OF Trigger

Write a PostgreSQL query to update a view that joins two tables. (Assume an INSTEAD OF trigger is defined to handle the update.)

Solution:

-- Update the department name via the EmployeeDepartmentView.
UPDATE EmployeeDepartmentView  -- This view joins Employees and Departments.
SET department_name = 'Human Resources'  -- Change the department name.
WHERE employee_id = 103;  -- Identify the employee record to update.

Explanation:

• Purpose of the Query:
• The goal is to update data in a view based on a join between two tables.
• This demonstrates that with an INSTEAD OF trigger defined on the view, the update is redirected to the appropriate underlying table(s).
• Key Components:
• UPDATE EmployeeDepartmentView : Specifies the view built on a join.
• SET department_name = 'Human Resources' : Modifies the department name column.
• WHERE employee_id = 103 : Targets a specific employee record.
• Real-World Application:
• Enables complex views to support updates even when they involve data from multiple tables.

Notes:

• Without an INSTEAD OF trigger (or appropriate rules), such views would not be directly updatable.

For more Practice: Solve these Related Problems:

• Write a PostgreSQL query to update an employee’s address via a view that joins Employees and Addresses, with an INSTEAD OF trigger redirecting the update.
• Write a PostgreSQL query to update a product’s price using a view that combines data from Products and Discounts, leveraging an INSTEAD OF trigger to propagate changes.
• Write a PostgreSQL query to update a customer’s membership level via a view that merges Customers and Memberships, using an INSTEAD OF trigger to handle the update.
• Write a PostgreSQL query to update a student’s enrollment status through a view that joins Students and Courses, with an INSTEAD OF trigger managing the update logic.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous PostgreSQL Exercise: Update Data through a View with WITH CHECK OPTION.
Next PostgreSQL Exercise: Refresh a Materialized View after Underlying Updates.