对非特定行，列或表的更新，插入，删除操作的SQL触发器 [英] SQL Trigger on Update, Insert, Delete on non-specific row, column, or table

问题描述

我有几个数据库供多个应用程序使用（其中一个数据库是我们自己的，其他数据库则无法控制它们的工作）。

I have several databases that are used by several applications (one of which is our own, the others we have no control over in what they do).

Out软件必须知道最近一次更改数据库的时间。由于某些原因，我不想赘述，因此我们决定为每个数据库使用一个具有单个字段的新表： last_changed_on 并具有一个 GetDate（）作为值。这样，我们自己的软件就可以检查上次更改的时间，并将其检查到该数据库的存储日期，如果该日期比内存中存储的日期新，则可以进行处理。

Out software has to know when the database has last been changed. For reasons I won't get into to keep this short we decided that going with a new table per database that has a singular field: last_changed_on that has a GetDate() as a value. This way our own software can check when it was last changed and check it to the date it has stored for said database and do things if the date is newer than what is stored in-memory.

进行了一些研究之后，我们决定与 Triggers 一起工作是可行的方法，但是根据我在网上可以找到的信息，触发器将查看您为Updates设置的特定列。

After doing some research we decided that working with Triggers was the way to go, but from what I could find online, triggers look at specific columns that you set for Updates.

我想知道的是，是否有一种方法可以自动执行该过程，或者只是明智地插入，更新，删除的触发器？

What I'd like to know is if there is a way to automate the process or just have a trigger that happens whenever anything happens insert, update, remove wise?

所以我正在寻找类似的东西：

So I am looking for something like this:

CREATE TRIGGER LastModifiedTrigger
ON [dbo].[anytable]
AFTER INSERT, UPDATE, DELETE
AS

   INSERT INTO dbo.LastModifiedTable (last_modified_on) VALUES (CURRENT_TIMESTAMP)

我知道上面的示例不是正确的触发器，我对他们来说还很陌生，所以我不是确定如何措辞。
可能有趣的是，我可以让自己的软件运行多个查询，从而自动为每个表和每个列创建查询，但我宁愿避免这样做，因为跟踪所有这些触发器将是从长远来看是痛苦的。
我希望每个数据库都尽可能少一些触发器，即使不必为每个单独的列名创建触发器。

I know that the above example isn't a correct trigger, I'm rather new to them so I was unsure on how to word it. It might be interesting to note that I can have my own software run several queries creating the queries automatically for each table and each column, but I'd rather avoid to do that as keeping track of all those triggers will be a pain in the long run. I'd prefer to have a little triggers per database as possible, if only by not having to make a trigger for each individual column name.

编辑：澄清：我试图避免创建一个自动脚本，该脚本去扫描每个表，然后依次扫描每个表的每个列，以创建触发器来查看那里是否有更改。目前我最大的问题是更新的触发行为，但我希望避免同时为 insert 和删除

To clarify: I am trying to avoid having to create an automated script that goes and scans every table, and sequentially every column of every table, to create a trigger to see if something is changed there. My biggest issue at the moment is the trigger behavior on updates, but I'm hoping to avoid having to specify tables as well for insert and delete

编辑2：为避免将来出现混乱，我在寻找针对SQL Server（MS SQL / T SQL）和MySQL的此问题的解决方案

编辑3：事实证明，我非常阅读该文档错误地（至少在MySql上），触发器在任何给定的更新列上都被激活，而不必定义特定的列。无论如何，我仍然想知道是否有一种方法可以使触发器少于数据库中每个表的触发器。 （即，对于任何类型的 update（）为1，对于任何类型的 insert（）为1，对于任何类型为1 delete（）

Edit 3: Turns out that I read the documentation very wrongly and (at least on MySql) the trigger activates on any given updated column without having to define a specific one. Regardless, I'm still wondering if there is a way to just have less triggers than having one for each table in a database. (i.e. 1 for any type of update(), 1 for any type of insert(), and 1 for any type of delete()

编辑4：忘记了该参数为了覆盖1个字段会带来性能问题，我已经考虑了这一点，并且现在正在处理多行。我还处理了3个触发器的创建（ insert（）， update（）和 delete（）），我真的希望这样做可以避免，但不能避免。

EDIT 4: Forgot that the argument for overwriting 1 field will come with performance issues, I've considered this and I'm now working with multiple rows. I've also handled the creating of 3 triggers (insert(), update(), and delete()) for each database through my software's code, I really wished this could've been avoided, but it cannot.

经过一堆互联网和更多的挖掘一直在寻找与我正在寻找的结果相反的结果，以及一堆反复试验后，我找到了解决方案。
首先，最重要的是：触发器不依赖于表（aka，该触发器为每个表都是不可能的，无法完成，这太糟糕了，将其排除在程序代码之外会很好，但是

After a bunch more digging on the internet and keep finding opposite results of what I was looking for, and a bunch of trial and error, I found a solution. First and foremost: having triggers not being dependent on a table (aka, the trigger activates for every table is impossible, it cannot be done, which is too bad, it would've been nice to keep this out of the program code, but nothing I can do about it.

第二：关于非特定于列的更新的问题是一个错误，这是由于我搜索触发器不依赖于特定列的原因仅给了我例子对于触发器。
以下解决方案适用于MySql，我尚未在SQL Server上对其进行测试，但我希望它不会有太大区别。

Second: the issue for updates on not being column specific was an error due to my part for searching for triggers not being dependent on specific columns only giving me examples for triggers that are. The following solution works for MySql, I have yet to test this on SQL Server, but I expect it to not be too different.

CREATE TRIGGER [tablename]_last_modified_insert
   AFTER INSERT/UPDATE/DELETE ON [db].[tablename]
     FOR EACH ROW
     BEGIN
        INSERT INTO [db].last_modified(last_modified_on)
        VALUES(current_timestamp())
     END

动态创建这些触发器，以下显示了我如何使用它：
第一个查询：

As for dynamically creating these triggers, the following show how I get it to work: First Query:

SHOW TABLES

我运行以上查询以获取数据库中的所有表，但不包括 last_modified 我做了一个自己，遍历了所有这些触发器，每个触发器创建了3个触发器。

I run the above query to get all the tables in the database, exclude the last_modified I made myself, and loop through all of them, creating 3 triggers for each.

非常感谢Arvo和T2PS的回复，他们的评论得到了为我指明正确的方向并提出解决方案。

A big thank you to Arvo and T2PS for their replies, their comments helped by pointing me in the right direction and writing up the solution.

推荐答案

在互联网上进行了更多挖掘之后，不断发现与我所寻找的结果相反的结果，并进行了大量的试验和错误，我找到了解决方案。
首先，最重要的是：触发器不依赖表（又称，为每个表激活该触发器是不可能的，它无法完成，这太糟糕了，它本来可以可以将其排除在程序代码之外，但我对此无能为力。

After a bunch more digging on the internet and keep finding opposite results of what I was looking for, and a bunch of trial and error, I found a solution. First and foremost: having triggers not being dependent on a table (aka, the trigger activates for every table is impossible, it cannot be done, which is too bad, it would've been nice to keep this out of the program code, but nothing I can do about it.

第二个：由于我的原因，更新不是列特定的问题
以下部分是针对MySQL的解决方案，我尚未在SQL Server上对其进行测试，但我希望它不会有太大的不同。 。

Second: the issue for updates on not being column specific was an error due to my part for searching for triggers not being dependent on specific columns only giving me examples for triggers that are. The following solution works for MySQL, I have yet to test this on SQL Server, but I expect it to not be too different.

CREATE TRIGGER [tablename]_last_modified_insert
   AFTER INSERT/UPDATE/DELETE ON [db].[tablename]
     FOR EACH ROW
     BEGIN
        INSERT INTO [db].last_modified(last_modified_on)
        VALUES(current_timestamp())
     END

动态创建在这些触发器上，下面显示了我如何使用它：
第一个查询：

As for dynamically creating these triggers, the following show how I get it to work: First Query:

SHOW TABLES

我运行上述查询以获取数据库中的所有表，但不包括 last_modified 我自己做完了，遍历所有这些触发器，每个触发器创建3个触发器。

I run the above query to get all the tables in the database, exclude the last_modified I made myself, and loop through all of them, creating 3 triggers for each.

这篇关于对非特定行，列或表的更新，插入，删除操作的SQL触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！