SQL Server-创建针对数据库中所有表运行的单个触发器 [英] SQL Server - Create single Trigger that runs for ALL tables in the database

问题描述

我正在尝试在SQL Server 2005中创建一个触发器，该触发器可在INSERT，UPDATE和DELETE上运行，但适用于数据库中的所有表（出于审计目的）。

I'm trying to create a trigger in SQL Server 2005 that runs on INSERT, UPDATE and DELETE, but for ALL tables in the database (for auditing purposes). Is it possible to do this?

当前我们对数据库中的每个表都有单独的触发器，并且由于它们都执行相同的操作，因此我希望将其合并

Currently we have separate triggers for every table in the database, and since they all do the same thing, I'm looking to consolidate them into a single trigger.

我知道可以创建数据库触发器，但是唯一可以挂接到的事件似乎是对表，存储过程等进行模式更改。

I know it's possible to create Database triggers, but the only events I can hook into seem to be for schema changes to tables, sprocs etc. but not for inserts and updates to records, unless I'm missing something?

推荐答案

通用表触发器在SQL中不存在，所以除非我缺少某些记录？您将需要遍历每个表（INFORMATION_SCHEMA.Tables），并使用动态SQL为每个表创建触发器。
（或者想出另一个简单的过程为每个表创建触发器。）

Generic table triggers don't exist in SQL so you'll need to loop through each of your tables (INFORMATION_SCHEMA.Tables) and create your triggers for each using dynamic SQL. (Or come up with another simple process to create triggers for each table.)

这篇关于SQL Server-创建针对数据库中所有表运行的单个触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！