且构网

分享程序员开发的那些事...
且构网 - 分享程序员编程开发的那些事

对非特定行,列或表的更新,插入,删除操作的SQL触发器

更新时间:2023-02-07 09:13:53

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

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.