SQL: triggers & views

As SQL is a programming language, you can trigger specific operations on specific events.

The other interesting feature we'll see here in short is the ability to create virtual tables that can store queries.

Triggering in SQL

You can target typical SQL events with a trigger:

CREATE TRIGGER my_trigger AFTER UPDATE
ON my_table FOR EACH STATEMENT
BEGIN
  -- YOUR OPERATION
END;

The above syntax is just an example, and you can tweak some parameters like the following depending on your goal:

• BEFORE or AFTER
• INSERT, UPDATE, or DELETE
• FOR EACH ROW or FOR EACH STATEMENT

Then, the game consists of running what you want inside the BEGIN...END block.

The coolest feature is you can run various arithmetic operations and other treatments inside such block, allowing relatively advanced logic.

Why using triggers?

You'll find many information about SQL triggers and how to master their syntax, but the "why" is important.

Triggers are used to automate some operations, like additional checking before or after someone or some program insert something in the targeted table.

It's also efficient to aggregate several columns.

Of course, you may use another programming language (not the one used for the database) to make similar operations, but using SQL triggers can ensure any event on the database will be included, not just the ones coming from the app.

It can be valuable for your business logic.

Using SQL views

You can create views to store specific queries and reuse them:

CREATE VIEW my_view AS
SELECT x.title, y.name
FROM table1 x
JOIN table2 y
ON x.id=y.ref_id;

Then, you may select the view directly:

SELECT * FROM my_view LIMIT 9;

Of course, the queries can be much more complex, but it's not the only purpose of SQL views.

Why using SQL views?

• you can store and reuse advanced queries, which save significant time
• you can implement access control: only provide what the end-user needs to see, nothing more

These are not the only usages but the most common ones in my experience.

Bottom line

Triggers and views are not magic, and there are valid reasons to use your own procedures instead of SQL triggers. For example, you might want need more advanced control you cannot implement with SQL.

SQL views can be a bit slower and are just SQL select in the end, not actual tables.

However, these features remain quite unknown while it can be quite beneficial for your app.

Comments

[Olivier Chavarin]

Incroyable : je cherche des infos sur les triggers, je me perds dans mes recherches... je procrastine et je tombe sur un profil usité sur Alsacréations, je me dis : "que devient Julien ?" je vais voir... dev.to... ha ! il a écrit des trucs sur les commandes SQL... sur les triggers... allons voir... Et hop : j'ai trouvé ce que je cherchais. Quelques ajustements pour mes besoins et ça semble fonctionner :

CREATE OR REPLACE FUNCTION update_date_modified()
RETURNS TRIGGER AS $$
BEGIN
  NEW._date_modified = CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
BEFORE UPDATE ON __account
FOR EACH ROW
EXECUTE FUNCTION update_date_modified();

Merci bien.

Bien amicalement,
Olivier C

[spO0q]

Cheers (dit-il des mois après - problème de notifications désactivées). Génial ! merci pour ce retour.