February 1, 2011 at 7:09 pm
I am building a database with a couple dozen tables. Most of them have a date_modified field, which I want to be automatically updated with getdate() whenever a row is modified. So before I create a couple dozen nearly identical triggers, I wanted to run my template by you guys for feedback.
It is based on Louis Davidson's template from the book "Pro SQL Server 2008...". So that's what the "utility.ErrorLog$insert" stored procedure is all about.
Is this the best way to do this? Is it overkill to have try/catch error handling for something so simple? Any and all feedback very much welcome!
USE [coasters]
GO
/****** Object: Trigger [pdl].[album_art_files_trg_upd] Script Date: 02/01/2011 17:22:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:AutoExcrement
-- Create date: 2011-02-01
-- Description:Updates date_modified when row is updated,
-- unless date_modified itself is specifically being updated.
-- =============================================
CREATE TRIGGER [pdl].[album_art_files_trg_upd]
ON [pdl].[album_art_files]
AFTER UPDATE
AS
BEGIN
DECLARE @rowsAffected int, --stores the number of rows affected
@msg varchar(2000); --used to hold the error message
SET @rowsAffected = @@rowcount;
--no need to continue on if no rows affected
IF @rowsAffected = 0 return;
SET NOCOUNT ON; --to avoid the rowcount messages
SET ROWCOUNT 0; --in case the client has modified the rowcount
BEGIN TRY
IF NOT UPDATE(date_modified)
UPDATE [pdl].[album_art_files]
SET [pdl].[album_art_files].[date_modified] = GETDATE()
FROM
inserted
WHERE
inserted.[album_art_files_id] = [pdl].[album_art_files].[album_art_files_id];
END TRY
BEGIN CATCH
IF @@trancount > 0
ROLLBACK TRANSACTION
EXECUTE utility.ErrorLog$insert
DECLARE @ERROR_MESSAGE nvarchar(4000)
SET @ERROR_MESSAGE = ERROR_MESSAGE()
RAISERROR (@ERROR_MESSAGE,16,1)
END CATCH;
END;
February 1, 2011 at 8:39 pm
I guess it would be more about your choice after comparing various approaches as well as depending upon how often the rows will be modified. However, in my personal opinion I would avoid using triggers as much as possible, rather I would use same SP that modifies the table to update the last modified date too.
utility.errorlog$insert might be an added overhead for trigger.
Swarndeep
http://talksql.blogspot.com
February 2, 2011 at 1:39 am
Auto, this usually isn't something you want to control at the trigger level unless you absolutely have to deal with dynamic SQL coming off a front end application. Are you familiar with the term CRUD wrapper?
CRUD = Create, Read, Update, Delete. They're standard wrapper procs you put on each table to allow you to perform these tasks via stored procedure instead of direct table access. Among other things, they act as your trigger control. It keeps your logic intact and more easily visible to other processes. I would recommend using that route instead of a trigger for this process.
Under most circumstances, you'll use triggers to maintain data integrity. So, as an example, when a new invoice is created, you'd update the customer's 'total ordered' column. That's a bad example of coding, but an example of when you'd actually use a trigger. The other time triggers are commonly used is as an auditing method, when storing to an audit table or the like.
This can be done this way, it's just uncommon and considered hidden overhead.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 2, 2011 at 12:26 pm
I love you guys. Thanks!
February 7, 2011 at 7:10 pm
Craig, do you think this set of SSMS tools would be appropriate? It claims to be able to autogenerate the CRUD wrappers you suggested. Seems like it will be important for me to be able to easily generate/manage these sprocs since there will be so many (4x~24 tables).
February 7, 2011 at 9:26 pm
autoexcrement (2/7/2011)
Craig, do you think this set of SSMS tools would be appropriate? It claims to be able to autogenerate the CRUD wrappers you suggested. Seems like it will be important for me to be able to easily generate/manage these sprocs since there will be so many (4x~24 tables).
'eh, seems reasonable enough. I typically generate my own. Each table tends to have its own oddities, though it can't hurt as a starting point.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 9, 2011 at 2:41 pm
autoexcrement (2/7/2011)
Craig, do you think this set of SSMS tools would be appropriate? It claims to be able to autogenerate the CRUD wrappers you suggested. Seems like it will be important for me to be able to easily generate/manage these sprocs since there will be so many (4x~24 tables).
I use these tools and they are great. You can setup the templates, so when you want to create CRUD procs, they all have the same format. One thing I do, though, is check each proc after it's created to add the documentation needed and define what the variables are for, etc... Don't just generate the procs and run them.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 13, 2011 at 11:17 am
Just my 2 cents...
I don't trust other people's code when it comes to the database especially since the data in most of my databases also has to stand up in a court of law. I also need to be able to catch any ad hoc manual queries which may do an update to the data. For that reason, I use a trigger I call an "LMB" trigger (LMB is short for "Last Modified By") which prevents changes to the DateCreated column, forces an update to the DateModified column, and forces an update to the LastModifiedBy column. In many cases, we also use Type 2 Slowly Changing Dimensions on data that will ultimately be delivered to the customer and we have a trigger does that, as well.
The use of Try/Catch to do error logging is just fine as the Catch code will only cause "extra overhead" if something goes very, very wrong.
As a side bar, it isn't necessary to declare a variable for @@ROWCOUNT tests unless you need it for the logging sproc. I also believe it's not necessary to do a SET ROWCOUNT because if the client set it, the same setting should be echoed in the trigger code behind the scenes.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply