question

ecomma avatar image
ecomma asked

Create trigger for all tables in a DB

I want to Create trigger on a database that would first check if similar trigger exist,if not it then create it.The For any insertion on any table in the database,it populate the DateCreated and DateModified Columns on that table.My current one is only doing it on one table. here is my code: USE [Issue] GO /****** Object: Trigger [dbo].[Update_DateCreated_DateModified] Script Date: 04/11/2013 09:51:20 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: -- Create date: -- Description: -- ============================================= 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) -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for trigger here UPDATE dbo.Patient SET DateCreated=@getDateCreated,DateModified=@getDateModified END GO After some research i came across a code that loop through tables and print the number of rows. Now i just need to integrate the two codes such that the script loops through all the tables and create the trigger above.Please help set nocount on declare @tablename varchar(90) declare @mycount varchar(90) SELECT @mycount = ' ' SELECT @tablename = ' ' while @tablename IS NOT NULL BEGIN SELECT @tablename = MIN(name) FROM sysobjects WHERE type = 'u' and name > @tablename IF @tablename IS NOT null BEGIN SELECT @mycount = 'select '''+@tablename+''', count(*) from ' + @tablename execute (@mycount) END END
sql-server-2008-r2trigger
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
Why do you want to do this with trigger? DEFAULT value will work. Assign default value getdate() for both columns, when you do inserts both columns will get the default value.
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I know default value can do it.But my Manager wants it that way.
0 Likes 0 ·
Any reason?
0 Likes 0 ·
@ecomma How's your CV looking? ;-)
0 Likes 0 ·
@ThomasRushton. Am a newbie to SQLSERVER
0 Likes 0 ·
@Cyborg please check the edit post
0 Likes 0 ·
Show more comments
ecomma avatar image
ecomma answered
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 ;'''
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.