如何在SQL Server中创建接受外部参数的触发器

本文关键字：外部 参数 触发器 创建 SQL Server | 更新日期: 2023-09-27 18:28:25

是否可以编写一个接受外部参数的触发器？

主要原因是，我被限制只能使用ONE数据库用户连接到数据库。现在我正在开发一个数据库应用程序，并为每个单独的表创建了触发器来存储历史。

在存储历史数据时，我希望存储执行了Insert、Update或Delete的应用程序用户名。我想使用User_Name()，但后来发现，它只返回数据库用户，而不是应用程序用户。

因此，我正在寻找一些关于创建具有外部参数的触发器的建议，以便将应用程序用户的id传递给该触发器。我想我已经清楚地解释了我的需求。提前感谢！

编辑
外部参数应该类似于存储过程参数，我们可以在其中传递值

第2版
可能是我的描述不够清晰。所以我给你举个例子
我正在开发的Web应用程序是Account Related。因此，要跟踪所需的数据更改。例如，会计人员可以更改员工的薪资和佣金信息。如果输入/更新了错误的信息，那么整体财务结果将产生巨大的负面影响。现在在系统中，用户使用userid&密码，然后对员工信息进行一些更改。在这个阶段，我想跟踪谁更改了员工信息，何时更改，更改前的值和更改完成后的值。这样，在未来，如果出现任何问题，我可以用PROOF找出错误的用户
不一定应该使用触发器来完成，但也欢迎任何其他选择。

如何在SQL Server中创建接受外部参数的触发器

您需要重新设计您的方法。数据库不可能只知道有关应用程序用户信息的任何信息。而且，不，您不能将参数传递给触发器。但您可以从触发器中引用自己的表，因此有一种方法。。。


当您连接到数据库时，每个连接都会获得自己的进程id。您可以通过@@spid获取该id。

所以，当你的触发器触发时，你可以用它来知道是哪个连接导致了变化。

使用相同的数据库登录名可以有100个并发连接，每个连接都有自己的@@spid值。


然而，要想对你有用，你需要准备好所有的人际关系。每次建立连接时，应用程序都应该写入一个表，记录使用该@@spid的应用程序。

也许像这样简单的东西

CREATE TABLE
  map_spid_application_user (
    spid          BIGINT,
    application   VARCHAR(128),
    user          VARCHAR(128),
    PRIMARY KEY (spid)
  )

然后，在每个连接上，运行这样的程序（可能通过存储过程）。。。

DELETE map_spid_application_user WHERE spid = @@spid
INSERT INTO map_spid_application_user SELECT @@spid, 'myApp', 'myUser'


然后，在您的触发器中，您可以在此表上引用/联接，以找出@@spid指的是谁。


你也可以变得更智能，并使用类似的方法来永久记录用户连接的内容，比如什么spid。任何spid的当前用户都将始终是具有最新连接日期时间的用户。


那时你几乎可以做任何你喜欢的事。因为你正在创建所有的信息，而不是依赖数据库来只知道除了@@spid之外的任何东西。

将用户名存储在每个表中，并在数据更改期间将其作为参数传入。从那里，触发器可以将数据归档到另一个表中。

在您的位置，我将使用此示例，如果在您的数据库中有人将进行事件更新或选择，您将能够添加一些信息，这是您想要的。

示例表：

CREATE TABLE RAF1
(
strona NUMBER,
nazwa VARCHAR2(40)
);

示例触发器（此触发器将始终，如果您将nazw="我"写入nazwa="蓝"，并且它连接到此表，则可以编辑此示例。

create or replace TRIGGER sam 
BEFORE INSERT OR UPDATE OF nazwa
 ON RAF1 FOR EACH ROW WHEN (new.nazwa = 'same')
 BEGIN :new.nazwa := 'ronald'; END

我已经完成了您描述的操作。我不能说我对结果感到满意，因为当发生类似连接共享之类的奇怪事情时，解决方案可能相当脆弱。这也只是感觉不对，但如果你喜欢，可以试试。

  1. 在应用程序端，将用户名填充到ConnectionString的AppName属性中
  2. 在触发器中，引用App_Name（）函数

同样，这将满足您的要求，但我不建议这样做。最好将审计信息作为客户端或存储过程的基本插入/更新内容的一部分进行管理。

因为触发器是按设计的niladic模块，这意味着它们不支持参数。在外部级别和幂零模块之间传递信息的一种技术是使用上下文信息或会话上下文。

Declare @mycontextinfo AS VARBINARY(128) = CAST('Application User Name' AS VARBINARY(128))
SET CONTEXT_INFO @mycontextinfo;

您可以从会话中的任何位置读取上下文信息，包括以下触发器：

SELECT CAST(CONTEXT_INFO() AS VARCHAR(128)) AS mycontextinfo;

另一种最复杂的方法是使用会话上下文；

EXEC sys.sp_set_Session_context @key = 'ApplicationUser', @value = 'XXX', @read_only = 1;

然后，当您需要从会话中的任何位置读取值时，您可以使用以下代码：

SELECT SESSION_CONTEXT('ApplicationUser') AS [USER]

请记住，这是在SQL Server 2016中添加的。

相关文章：