3

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.

  • 5
    What problem are you trying to solve with this? It feels very heavy-weight in terms of impact. Could you also add your version of SQL Server to the tags please. – George.Palacios Dec 13 '18 at 15:54
  • @George.Palacios I'm trying to have the database track when the model was last updated which requires all tables within the database to be monitored for update so that the model DateLastEdit field can be updated, am I perhaps going about this the wrong way? – J. Spence Dec 13 '18 at 16:02
  • How up to date does it need to be? (Can it be seconds, minutes, hours or days out of date) Do you use stored procedures only or do you also need to track ad-hoc T-SQL? – George.Palacios Dec 13 '18 at 16:04
  • 3
    @J.Spence Please add this extra info to the question for readability. If this is going to be done via SP, why not add the logic to all the SPs and/or have them each call a single SP to update this table's value? – LowlyDBA Dec 13 '18 at 16:14
  • 1
    @J.Spence It still may be a heavy handed, really depends on your workload like others have mentioned...but I would prefer that over a trigger whenever possible (personally). You'll have to test to see if its a problem or not most likely. – LowlyDBA Dec 13 '18 at 16:23
4

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.

  1. 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.

  2. 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

  3. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.