SQL Server/Oracle表只能由触发器写吗? [英] SQL Server/Oracle table writable only by a trigger?

问题描述

我想知道是否有一种方法可以使表只能由SQL Server或Oracle DB中的特定触发器写入. 只是为了举例:

I am wondering if is there a way to make a table writable only by an specific trigger in SQL Server or Oracle DB. Just to make an example:

Table: "Operation"

ID |日期| Account1_ID | Account2_ID |金额

---

Table: "Transactions"

ID |日期|帐号|债务|信用

---

我要确保的是，交易表仅从 Operation 表中的触发器接收数据.

---

What I want to ensure is that the Transactions table only receive data from a trigger in the Operation table.

有没有办法做到这一点?

Is there a way to achieve that?

推荐答案

执行为允许您创建一个以不同于当前用户的用户身份运行的触发器. 因此，您可以创建一个对TRANSACTIONS表具有写权限的单独用户，然后以该用户身份执行触发器.

Execute As allows you to create a trigger that runs as a user different to the current user. So you can create a separate user who has write access to your TRANSACTIONS table, and execute the trigger as that user.

如果没有其他用户具有写访问权限，则只有触发器用户才能修改数据.

If no other users have write access, only the trigger user can modify the data.

然后，您当然需要确保没有人可以该用户身份登录，并且您可以信任DBA来管理该帐户.

Of course you then need to make sure nobody can log in as that user, and that you can trust your DBA to manage the account.

这篇关于SQL Server/Oracle表只能由触发器写吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！