0

I have approximately 15 tables that all have a column titled "modifieddate" and i want to create a trigger that will update that date upon record update. is there a way to do that with one trigger or do I need multiple ones? i also could use the code to do so as i am struggling with it.

I have tried the code but it does not work.

CW_PM
  • 3
  • 1
  • What code have you tried? You can update muliple tables with a trigger, but I am not sure you should... – Neo Nov 08 '16 at 15:47
  • 1
    You will have to create a trigger for each table, there is no way around that. You could do it in your client code but a trigger is better because there is no way around it, it will always happen no matter what – GuidoG Nov 08 '16 at 15:48
  • Why do you need to update all of the table's modified date? You can always select the max modified date joining all of the tables for each id to get a last modified date. Updating all of the tables is actually removing data as you don't know downstream, which table was actually modified last. – Michael buller Nov 08 '16 at 16:51

1 Answers1

0

A trigger on each table would work, e.g.:

CREATE TRIGGER [dbo].[Customer_update_trigger]
       ON [dbo].[Customers]
AFTER UPDATE
AS
BEGIN

    SET NOCOUNT ON;

    UPDATE dbo.Customer
    SET ModfiedDate = GETDATE()     

END

However, you might want to put the logic in the application instead. For example, if you look at GETDATE() above, it isn't very flexible. The application would allow more flexibility.

Joe Ratzer
  • 18,176
  • 3
  • 37
  • 51