多表触发器 SQL Server 菜鸟 [英] Multi table Triggers SQL Server noob

问题描述

我有很多表都具有相同的 2 个日期时间列(lastModDate、dateAdded).我想知道是否可以为这些表设置全局插入更新触发器来设置日期时间值.或者如果没有，有哪些方法?

I have a load of tables all with the same 2 datetime columns (lastModDate, dateAdded). I am wondering if I can set up global Insert Update trigger for these tables to set the datetime values. Or if not, what approaches are there?

非常感谢任何指针

推荐答案

您可以将 DEFAULT 值用于插入 (dateAdded) 并为 UPDATE 使用 TABLE 触发器.

You can use DEFAULT values for the inserts (dateAdded) and a TABLE trigger for the UPDATE.

类似的东西

CREATE TABLE MyTable (
        ID INT,
        Val VARCHAR(10),
        lastModDate DATETIME DEFAULT CURRENT_TIMESTAMP, 
        dateAdded DATETIME DEFAULT CURRENT_TIMESTAMP
)
GO

CREATE TRIGGER MyTableUpdate ON MyTable
FOR UPDATE
AS
UPDATE  MyTable
SET     lastModDate = CURRENT_TIMESTAMP
FROM    MyTable mt INNER JOIN
        inserted i ON mt.ID = i.ID
GO

INSERT INTO MyTable (ID, Val) SELECT 1, 'A'
GO

SELECT *
FROM MyTable
GO

UPDATE MyTable
SET Val = 'B' 
WHERE ID = 1
GO

SELECT *
FROM MyTable
GO

DROP TABLE MyTable
GO

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