英文:
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操作,同时访问NEW
和OLD
行的值(触发器和检查),它们之间的真正区别是什么?选择它们取决于函数体吗?
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.
-
一个检查约束只能访问表中的其他列,并且只能通过表达式而不是函数来进行操作。您可以使用函数创建检查约束,但Postgres不会跟踪该依赖关系。因此,在进行升级时,例如通过dump/restore,您将会收到错误,因为表创建命令将寻找一个尚不存在的函数。此外,检查约束无法访问NEW/OLD,并且不能更改表中其他字段的值。
-
触发器必须从一个函数中运行其代码。该代码可以触及数据库中的几乎任何内容。该函数具有根据事件(INSERT/UPDATE/DELETE)或时机(BEFORE/AFTER)的不同而访问NEW和/或OLD的权限。
英文:
A check constraint and a trigger are two different things.
-
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.
-
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).
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论