Multi table SQL Server noob triggers

Question

I have loading tables with the same two datetime columns (lastModDate, dateAdded). I am wondering if I can configure the Insert Update global trigger for these tables to set datetime values. Or, if not, what approaches are there?

Any pointers very valuable

Answer 1

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

Sort of

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 

Answer 2

I agree that there is no such global trigger, but we can, of course, reduce our efforts by creating a script that will generate triggers for tables.

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

Answer 3

No, there is no such thing as a “global” trigger or multi-table triggers. Design triggers are tied to a table, so if you need to have triggers when loading tables, you need to create loading triggers, one for each table, and expand them. I am afraid that this is not so.

Answer 4

since the code will be the same and only table_name will be changed ... I think that it is best to create a procedure and then call this procedure from each trigger

Answer 5

Generate triggers for all tables

Well, I did it initially to generate triggers for all tables in the database to check for data changes, and it's simple enough, just move the entire row from the remote table to the mirrored audit table.

But someone wanted to track activity on tables, so this is a little easier. Here we create one log table, and whenever a dml operation is performed, 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