Database-wide trigger I'm new to DBA and I'm attempting to create a trigger that will update the a specified column in one table whenever an insert is made into every table across the database.

I understand it is possible to implement many triggers to solve this problem however it seems to me that it would be much more maintainable if it were to be a single trigger activating at a database wide level.

If having a single trigger is possible, is there then also reason I should not have one trigger to manage all of the tables?

Below is the current way in which I would implement the triggers at a table by table basis:

CREATE TRIGGER [dac].[T_UpdateModelTime] ON [dac].[SubSpeciality]
 AFTER INSERT
 AS

 DECLARE @Table_Model_ID int

 SELECT @Table_Model_ID = Model_ID FROM SubSpeciality 

 UPDATE ModelVersion SET DateLastEdited = GETDATE() WHERE ID = @Table_Model_ID;

GO




Additional details

The data is inserted into the tables through stored procedures.

The updated column requires time sensitivity within seconds.
Can I add a single trigger that will fire for every UPDATE, INSERT or DELETE on every table in a database?

No. As per the documentation each TRIGGER must resolve down to columns updated on a single table.

There is no magic solution to your problem, but there are several generic approaches that are generally used.


Utilise Stored Procedures to update a single table holding the records of the last update time of all other tables - this would need to be written into every Stored Procedure that runs an INSERT, UPDATE or DELETE.
Change Data Capture or Change Tracking could also be used if you set up some SQL Agent Jobs to check for updates on a regular basis. Pinal Dave has a great guide to using CDC for this here
Table-level DML triggers - as per your own post. This is likely to have a significant performance overhead and is likely a really bad idea.