February 14, 2008 at 9:59 am
I have an organizational question. SQL Server 2005 Database has 25 tables and each one in addition to it's other information has four fields
createdBy
createdAt
changedBy
changedAt
The trigger below accomplishes the objective of stamping both the date and who made the last change to the data. I know that it can get alot more complicated than this however -- this is sufficent for this application.
Here is my question: Can the code below be encapsulated so that all the tables are sharing the same logic. I really appreciate any help. It seems that I should be able to call a function or procedure that inserts the table name and this should be stored in one place not on every single table.
USE [M_Org]
GO
/****** Object: Trigger [dbo].[usrUpdatedBy] Script Date: 02/14/2008 08:38:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:Julie Bernhardt
-- Create date: 2/13/2008
-- Description:Update Stamp
-- =============================================
ALTER TRIGGER [dbo].[usrUpdatedBy]
ON [dbo].[tblMasterAccount]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
Update dbo.tblMasterAccount set ChangedAt = default
Update dbo.tblMasterAccount set ChangedBy = User
END
February 16, 2008 at 8:58 am
First off, how are the tables being updated? I normally put this kind of work in the stored procedure/front end doing the updating. The only way to encapsulate this code in one place is to use a stored procedure and dynamic SQL that you call in the trigger on each of the tables. The stored procedure would be like this:
Create Procedure usp_set_changeby_change_at
(
@table_name sysname,
@primary_key_field sysname, @primary_key_value varchar(100)
)
AS
Set NoCount On
Declare @sql varchar(500)
Set @sql = 'Update ' + @table_name + ' Set changedBy = SUSER_SNAME(), changedAt = getdate() where ' + @primary_key_field + '=''' + @primary_key_value + ''''
Exec sp_executesql @sql -- or Exec(@sql)
Return
Then your trigger(s) would call this sp with the appropriate paramters. But you would have to loop through the inserted table in order to handle a batch update.
As a side note the example code you posted will update every row in the table when any row is updated. If this is the actual code you would want to change your trigger code to something like:
ALTER TRIGGER [dbo].[usrUpdatedBy]
ON [dbo].[tblMasterAccount]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if update(changedBy) or Update(ChangedAt)
Begin
Return -- don't need to fire trigger on update of changedby or changed at columns
End
-- Insert statements for trigger here
Update MA
Set changedAt = getdate(),
changedBy = Suser_Sname()
From
dbo.tblMasterAccount MA Join
inserted I On
MA.[primary_key_field] = I.[primary_key_field]
END
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply