Скачать или смотреть How to Write an Instead of Update Trigger Correctly in PostgreSQL

A comprehensive guide on creating an `instead of update` trigger in PostgreSQL, including examples and clarifications on how updates in tables and views work.
---
This video is based on the question https://stackoverflow.com/q/68470749/ asked by the user 'ceving' ( https://stackoverflow.com/u/402322/ ) and on the answer https://stackoverflow.com/a/68471686/ provided by the user 'Laurenz Albe' ( https://stackoverflow.com/u/6464308/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to write an instead of update trigger correctly?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Understanding Instead of Update Triggers in PostgreSQL

In the world of database management, one common requirement is to update views when alterations are made. However, the process can get complex, especially when using instead of update triggers. In this guide, we'll break down the intricacies of writing such a trigger correctly, using PostgreSQL as our model.

The Scenario: Tables and Views

Let's consider a simple database example. Take a table named t and a view named v, which are defined as follows:

[[See Video to Reveal this Text or Code Snippet]]

Your objective is to create an instead of update trigger for the view v so that any updates in the view will reflect on the underlying table t. Although PostgreSQL can handle simple view updates automatically in straightforward scenarios, more complex use cases may necessitate the implementation of an explicit trigger.

Implementing the Trigger

Here's how the trigger is traditionally written:

[[See Video to Reveal this Text or Code Snippet]]

Breaking Down the Components

Creating the Trigger Function:

CREATE FUNCTION u() creates a new function that will handle the update logic.

The statement UPDATE t SET a = NEW.a, b = NEW.b, c = NEW.c; instructs PostgreSQL to update columns in table t based on new values supplied during the view update.

Creating the Trigger:

CREATE TRIGGER t INSTEAD OF UPDATE ON v creates the trigger that will call function u() whenever an update on view v occurs.

Clarifying Update Mechanics

It is essential to understand how PostgreSQL handles updates:

Table Updates: When you perform an UPDATE on table t, it may impact a single column, but PostgreSQL writes a new version of the entire row, not just the altered column.

View Updates: Similarly, updating the view v might not seem direct, but it will also invoke the trigger, leading to updates on multiple columns based on the defined function.

For instance, consider the following queries:

[[See Video to Reveal this Text or Code Snippet]]

The Efficiency Question

You may wonder if using triggers introduces performance issues:

Trigger Overhead: Every time you invoke an update on the view, there is a slight overhead due to the trigger execution. This might make updates on views slower than direct table updates.

Do You Even Need the Trigger?

In many cases, such as the example given, the trigger might be unnecessary. Simple views that do not involve complex logic are typically automatically updateable in PostgreSQL. This means you can update the view directly without defining additional triggers.

Conclusion: Navigating Update Operations

In conclusion, while defining an instead of update trigger is crucial for intricate scenarios, it is equally essential to recognize when it is not needed. Understanding how PostgreSQL handles updates—both at the table and view level—will not only simplify your database management but also enhance performance.

Next time you tackle triggers, evaluate if they are needed based on your specific use case. For many simple updates, you might find that PostgreSQL's built-in functionalities will suffice!

So, are triggers your necessity or just an extra step? Happy querying!