Multi table Triggers SQL Server noob

问题:

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?

Any pointers much appreciated

回答1:

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

Something like

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

回答2:

I agree there is no such Global Trigger, but we can certainly reduce our efforts by creating script which will generate triggers for the tables.

Something like: http://weblogs.sqlteam.com/brettk/archive/2006/11/29/35816.aspx

回答3:

No, there's no such thing as a "global" trigger or a multi-table triggers. Triggers are by design bound to a table, so if you need to have triggers on a load of tables, you need to create a load of triggers, one for each table, and deploy them. No way around that, I'm afraid.

回答4:

since the code will be same and only table_name will be changed... i think that best is to create procedure and then call this procedure from every trigger

回答5:

Generate Triggers for all Tables

Well, I did this originally to generate triggers for all tables in a database to audit data changes, and that is simple enough, just move the entire row from the deleted table to a mirrored audit table.

But someone wanted to track activity on tables, so it's a little more simple. Here we create one log table, and any time a dml operation occurs, it is written there.

Enjoy

USE Northwind GO

CREATE TABLE LOG_TABLE (Add_dttm datetime DEFAULT (GetDate()),
TABLE_NAME sysname, Activity char(6)) GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname SET NOCOUNT ON

SELECT @TABLE_NAME = MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHILE @TABLE_NAME IS NOT NULL   BEGIN  SELECT @sql = 'CREATE TRIGGER
[' + @TABLE_NAME + '_Usage_TR] ON [' + @TABLE_NAME +'] '   + 'FOR
INSERT, UPDATE, DELETE AS '   + 'IF EXISTS (SELECT * FROM inserted)
AND NOT EXISTS (SELECT * FROM deleted) '   + 'INSERT INTO LOG_TABLE
(TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''', ''INSERT''' + '
'   + 'IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM
deleted) '   + 'INSERT INTO LOG_TABLE (TABLE_NAME,Activity) SELECT '''
+ @TABLE_NAME + ''', ''UPDATE''' + ' '   + 'IF NOT EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted) '   + 'INSERT INTO
LOG_TABLE (TABLE_NAME,Activity) SELECT ''' + @TABLE_NAME + ''',
''DELETE''' + ' GO'  SELECT @sql  EXEC(@sql)  SELECT @TABLE_NAME =
MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables WHERE TABLE_NAME >
@TABLE_NAME    END SET NOCOUNT OFF