troubleshooting Question

SQL Sever 2008 Automated solution for trigger creation required.

Avatar of splanton
splantonFlag for United Kingdom of Great Britain and Northern Ireland asked on
Microsoft SQL ServerMicrosoft SQL Server 2008
11 Comments3 Solutions347 ViewsLast Modified:
Hiya,

I am re-visiting a database and adding 'Created' and 'Modified' Date fields to each table and adding the corresponding triggers.

My code looks like this:

ALTER TABLE ContractorPrice ADD [Created] [datetime] NULL;
GO
ALTER TABLE ContractorPrice ADD [Modified] [datetime] NULL;
GO

CREATE TRIGGER utr_ContractorPrice_Insert ON dbo.ContractorPrice
FOR INSERT
AS
Begin
Update ContractorPrice
set Created = getdate ()
from inserted i, ContractorPrice a
where i.ContractorPriceId = a.ContractorPriceId
End
GO

CREATE TRIGGER utr_ContractorPrice_Update ON dbo.ContractorPrice
FOR UPDATE
AS
Begin
Update ContractorPrice
set Modified = getdate ()
from inserted i, ContractorPrice a
where i.ContractorPriceId = a.ContractorPriceId
End
GO

Fortunatly all the primary key fields are single fields and all follow the same naming convention (e.g. 'tablenameId')

Now I could write individual scripts for each table in the database but I'm sure there has to be a natty way of doing this using the system tables to drive an iterative process to create all the fields and triggers.

Not only would this be prefereable from a time point of view it would be less likely to incur typos etc.

Any examples would be greatly appreciated.

Regards.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 3 Answers and 11 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros