Dealing with Stamp and StampUpdated with Transfer

Continuing work on my top secret app and learning CCT (ColdBox, ColdSpring, and Transfer), I'm going to describe how to handle stamp and stampupdated columns in your tables. In every table that I have in any database I build, I have both a stamp and stampupdated column. The stamp column tells me when the record was originally created, and the stampupdated column tells me the last time that data changed. Both of these columns have a default value of getDate(). Here's the colum definition for an example table: CREATE TABLE [dbo].MyTable( [ID] [int] IDENTITY(1,1) NOT NULL, [Label] [varchar](50) NOT NULL, [stamp] [datetime] NOT NULL CONSTRAINT [DF_Items_stamp] DEFAULT (getdate()), [stampupdated] [datetime] NOT NULL CONSTRAINT [DF_Items_stampupdated] DEFAULT (getdate()), This works great on initial insert, but doesn't update the stampupdated column on each UPDATE. So to handle making sure the stampupdated gets taken care of properly I create a trigger on each table like so: CREATE TRIGGER [dbo].[MyTable_Update] ON [dbo].[MyTable] 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 MyTable SET stampupdated = getDate() FROM Inserted WHERE MyTable.ID = Inserted.ID END Now to handle these properly in Transfer, I can't have transfer updating and inserted those values, because the database is responsible for those actions. So let's take a look at what would be the standard property tags for those two columns: So with the above property tags, when I save a transfer object it's going to run either an INSERT or an UPDATE statement that will change the stamp and stampupdated columns. But since the database is responsible for this data, that means that 1) Transfer is doing unnecessary and potentially incorrect work and 2) That the values that the database creates won't get back to the Transfer object. To fix that we need to add a few additional attributes to each of these property tags. I've now told Transfer to ignore the stamp for the purposes of INSERTs and UPDATEs, and to update the Transfer value when a new record is inserted. I don't need to refresh the value on UPDATE, as the stamp value should never change. On the stampupdated column however, I'm ignoring the value for both INSERTs and UPDATEs, and I'm also refreshing the value on both. This means that on an update the workflow of the UPDATE works something like this: * Call Transfer save method * Record is UPDATEd in the database * On UPDATE trigger fires and updates stampupdated column to the current date * Transfer queries the update record, picks up the new stampupdated column, and updates the stampupdated property of the object to the new value * Transfer gives me back a fresh object with the new values So that's how I handle all of my stamp and stampupdated columns in my database. You can find out more about the Transfer config options at the Transfer ORM docs site.
Posted by Daniel Short on Jul 20, 2008 at 12:00 AM | Categories: ColdFusion - Transfer -

Comments