check constraints vs. triggers while both of them written in PLPg/SQL

huangapple go评论54阅读模式
英文:

check constraints vs. triggers while both of them written in PLPg/SQL

问题

I am using PostgreSQL 15.2; I can use both check constraints and triggers to ensure my business logic on the database.

我正在使用PostgreSQL 15.2;我可以同时使用检查约束和触发器来确保我的数据库上的业务逻辑。

I read some of the comparisons about them, and I still have a question: if we can use PLPG/SQL for both of them and inside that custom function, we could user any select/insert/update/delete on any row on the same table or other tables, with accessing to NEW and OLD values of a row in both of them (trigger and check), what is the real difference between them? is choosing between them depend on the function body ?

我阅读了一些它们之间的比较,但我仍然有一个问题:如果我们可以在两者中都使用PLPG/SQL,并在自定义函数中使用它们,我们可以在相同的表或其他表上使用任何select/insert/update/delete操作,同时访问NEWOLD行的值(触发器和检查),它们之间的真正区别是什么?选择它们取决于函数体吗?

I tried both ways in some cases. I don't know the performance effects.

在某些情况下,我尝试了两种方法。我不知道性能效果。

英文:

I am using PostgreSQL 15.2; I can use both check constraints and triggers to ensure my business logic on database.

I read some of the comparisons about them, and I still have a question: if we can use PLPG/SQL for both of them and inside that custom function, we could user any select/insert/update/delete on any row on the same table or other tables, with accessing to NEW and OLD values of a row in both of them (trigger and check), what is the real difference between them? is choosing between them depend on the function body ?

I tried both ways in some cases. I don't know the performance effects.

答案1

得分: 0

A check constraint and a trigger are two different things.

  1. 一个检查约束只能访问表中的其他列,并且只能通过表达式而不是函数来进行操作。您可以使用函数创建检查约束,但Postgres不会跟踪该依赖关系。因此,在进行升级时,例如通过dump/restore,您将会收到错误,因为表创建命令将寻找一个尚不存在的函数。此外,检查约束无法访问NEW/OLD,并且不能更改表中其他字段的值。

  2. 触发器必须从一个函数中运行其代码。该代码可以触及数据库中的几乎任何内容。该函数具有根据事件(INSERT/UPDATE/DELETE)或时机(BEFORE/AFTER)的不同而访问NEW和/或OLD的权限。

英文:

A check constraint and a trigger are two different things.

  1. A check constraint should only access other columns in the table and only through an expression, not a function. You can create a check constraint with a function but Postgres does not track that dependency. So when you do an upgrade , say via dump/restore you will get errors as the table creation command will be looking for a function that does not exist yet. Also a check constraint does not have access to NEW/OLD. Furthermore it cannot change values for other fields in the table.

  2. A trigger has to run its code from a function. That code can touch pretty much anything in the database. The function has access to NEW and/or OLD depending on the event(INSERT/UPDATE/DELETE) or timing(BEFORE/AFTER).

huangapple
  • 本文由 发表于 2023年5月6日 22:22:21
  • 转载请务必保留本文链接:https://go.coder-hub.com/76189400.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定