0

Am trying to Create a function that would loop through all the tables in a database and Create a trigger if it does not exist.After some research i came across the store procedure:"sp_MSforeachtable procedure" Can some one help me with a code that would use the function to create the trigger below.The Trigger works fine just that now i need to make sure it is applied on all table using a loop.NB I don't want to manual run it on all table.Performance wise i know may not be the best idea to have triggers on all tables in a database. here is my trigger

USE [Issue]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Update_DateCreated_DateModified]
ON  [dbo].[Patient] 
FOR  INSERT
AS 
BEGIN
DECLARE @getDateCreated Datetime = GETDATE()
DECLARE @getDateModified DATETIME=GETDATE()
DECLARE @patient_nin VARCHAR(50) = (SELECT NIN FROM INSERTED i)
SET NOCOUNT ON;
UPDATE dbo.Patient SET DateCreated=@getDateCreated,DateModified=@getDateModified
END   
GO
kombo
  • 655
  • 3
  • 11
  • 26

1 Answers1

1

The following code is NOT tested, but it should be something like this.

EXEC sp_MSforeachtable 'IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N''?_Update_DateCreated_DateModified''))
EXEC dbo.sp_executesql @statement = 
N''CREATE TRIGGER [dbo].[?_Update_DateCreated_DateModified]
ON  ?
FOR  INSERT
AS 
BEGIN
DECLARE @getDateCreated Datetime = GETDATE()
DECLARE @getDateModified DATETIME=GETDATE()
DECLARE @patient_nin VARCHAR(50) = (SELECT NIN FROM INSERTED i)
SET NOCOUNT ON;
UPDATE ? SET DateCreated=@getDateCreated,DateModified=@getDateModified
END   
;'''

The ? is replaced with the table name. If I remember correctly

Edwin Stoteler
  • 1,218
  • 1
  • 10
  • 25
  • I Got this error:Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '+'. – kombo Apr 12 '13 at 09:14
  • ,since the code is looping through all the tables.It means the ? cannot be static variable.Is this right? – kombo Apr 12 '13 at 09:24
  • another error: Msg 102, Level 15, State 1, Procedure Update_DateCreated_DateModified, Line 12 Incorrect syntax near 'GO'. – kombo Apr 12 '13 at 09:26
  • @kombo ? is replaced with schema.table. You can use it like it is used here: [link](http://stackoverflow.com/questions/9679997/sql-server-sp-msforeachtable-usage-to-select-only-those-tables-which-meet-some-c). Try replacing GO with ; – Edwin Stoteler Apr 12 '13 at 09:27
  • @kombo BTW becarefull this is excecuted for ALL tables, not just those in dbo schema. – Edwin Stoteler Apr 12 '13 at 09:39
  • thanks @NLwino. i appreciate your help.Command(s) completed successfully. – kombo Apr 12 '13 at 10:26
  • i create two tables on the database now but it creates the trigger on one table and then display the error:Msg 2714, Level 16, State 2, Procedure Update_DateCreated_DateModified, Line 10 There is already an object named 'Update_DateCreated_DateModified' in the database. – kombo Apr 12 '13 at 10:41
  • @kombo Edited which should solve the problem. Triggernames are now: TableName_Update_DateCreated_DateModified. This will create new triggers for all tables, so delete your old ones that do the same job. – Edwin Stoteler Apr 12 '13 at 10:46