php - 如何根据父表自动更新子表中的列值？

Question

My web app architecture for user access separated in two ways:- Manager Table (column: privilege) Employee Table (column: privilege, permission)

Where Manager is a Super-Admin account which will be used to subscribe our service based on package type(eg: basic, silver, gold).

For Employee, this acc will be created by Manager and share the same privilege of Manager by default(the subscribed service package). However, the Manager will need to set a permission level for each function in order for the Employee to be able to access the function in the web app.

The idea is, if session by manager acc, no permission needed. if session by employee, permission needed.

问题:当 Child 表中的新行创建时，'privilege' 列的默认值应遵循与 Parent 相同的值。此外，如果父级降级权限，子列值也应随之降级。

有没有办法从数据库的角度实现这一目标？我正在使用 postgresql。我知道这绝对可以从后端实现，但是每个 CRUD 操作都需要做很多逻辑。

Answer 1

您可以尝试使用触发器或存储过程并将逻辑放在数据库中。