Dr. Blakeyrat's Mean Bean History Table Machine



  • I wrote this monstrosity of a T-SQL stored procedure to automatically make history tables:

    create or alter procedure CreateHistoryTable
    	@tableName nvarchar(max)
    as
    begin
    
    	--declare @tableName nvarchar(max)
    	--set @tableName = 'User'
    
    	declare @createTableTemplate nvarchar(max)
    	declare @createUpdateTriggerTemplate nvarchar(max)
    	declare @createDeleteTriggerTemplate nvarchar(max)
    
    	set @createTableTemplate = N'
    			select *,
    				CAST(null as nvarchar(50)) as ''HistoryChangeEvent'',
    				CAST(null as datetime) as ''HistoryChangeTime''
    			into [history].[<tableName>]
    			from [dbo].[<tableName>]
    			where 1 = 0
    			union all
    			select *,
    				CAST(null as nvarchar(50)) as ''HistoryChangeEvent'',
    				CAST(null as datetime) as ''HistoryChangeTime''
    			from [dbo].[<tableName>]
    			where 1 = 0
    		';
    
    	set @createUpdateTriggerTemplate = N'
    			create or alter trigger [dbo].[<tableName>UpdateHistory]
    			on [dbo].[<tableName>]
    			for update
    			as
    			begin
    				insert into [history].[<tableName>]
    				select *, ''update'', GETDATE()
    				from deleted
    			end
    		';
    
    	set @createDeleteTriggerTemplate = N'
    			create or alter trigger [dbo].[<tableName>DeleteHistory]
    			on [dbo].[<tableName>]
    			for delete
    			as
    			begin
    				insert into [history].[<tableName>]
    				select *, ''delete'', GETDATE()
    				from deleted
    			end
    		';
    
    	declare @createTableQuery nvarchar(max)
    	declare @createUpdateTriggerQuery nvarchar(max)
    	declare @createDeleteTriggerQuery nvarchar(max)
    
    	set @createTableQuery = replace( @createTableTemplate, '<tableName>', @tableName )
    	set @createUpdateTriggerQuery = replace( @createUpdateTriggerTemplate, '<tableName>', @tableName )
    	set @createDeleteTriggerQuery = replace( @createDeleteTriggerTemplate, '<tableName>', @tableName )
    
    	exec( @createTableQuery )
    	exec( @createUpdateTriggerQuery )
    	exec( @createDeleteTriggerQuery )
    
    end
    go
    

    Am I a horrible person? y/n

    Seriously, though, I'd appreciate advice on how and when this is going to explode in my face. But based on my initial testing, it looks solid...


  • And then the murders began.

    @blakeyrat

    Not seeing the point of the UNION ALL in the CreateTableTemplate, though I may be missing some subtlety there.

    Any schema changes to the source table require a corresponding change to the history table, or dropping & recreating the history table. (Which if you're okay with that, that's fine. Our history tables combat that by just having FieldName/FieldValue, and therefore the triggers get appropriately gnarly to insert one row per changed field.)

    No trigger on CREATE so you don't know when the records were first set up.

    Otherwise... seems reasonably solid to me?


  • Impossible Mission Players - A

    @blakeyrat

    Looks pretty solid as far as creating the history tables and the triggers for preserving history. Have you tested your regular workload against the triggers to verify day to day transactions won’t be impacted too severely by the triggers? From what I’ve seen, that’s usually where history table logging is going to go wrong, if something bad is going to happen.



  • @unperverted-vixen said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Not seeing the point of the UNION ALL in the CreateTableTemplate, though I may be missing some subtlety there.

    Fun quirk of creating tables using select into: if the original table has an autonumber, the new table will as well. But for a history table, obviously I don't want an autonumber because it'll prevent me from adding history, how to fix?

    Docs say:

    When an existing identity column is selected into a new table, the new column inherits the IDENTITY property, unless one of the following conditions is true:

    • The SELECT statement contains a join.
    • Multiple SELECT statements are joined by using UNION.
    • The identity column is listed more than one time in the select list.
    • The identity column is part of an expression.
    • The identity column is from a remote data source.

    Ok; well, let's see what we can do here. Join? No, that'd create too many output columns. Column listed more than once? That would require my query to know the column names, and it don't. Column is part of an expression? Ditto. Remote data source? Ain't got one. What's left?

    Doing a union.

    I'm particularly proud of that idiotic moron workaround.

    @unperverted-vixen said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Any schema changes to the source table require a corresponding change to the history table, or dropping & recreating the history table. (Which if you're okay with that, that's fine. Our history tables combat that by just having FieldName/FieldValue, and therefore the triggers get appropriately gnarly to insert one row per changed field.)

    True; when/if that becomes an issue, I'll deal with it.

    @unperverted-vixen said in Dr. Blakeyrat's Mean Bean History Table Machine:

    No trigger on CREATE so you don't know when the records were first set up.

    Not currently part of the requirements, but adding a "created" datetime on the original "live" tables might not be a bad idea.

    @unperverted-vixen said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Otherwise... seems reasonably solid to me?

    Yay. It looks like garbage.

    @izzion said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Have you tested your regular workload against the triggers to verify day to day transactions won’t be impacted too severely by the triggers? From what I’ve seen, that’s usually where history table logging is going to go wrong, if something bad is going to happen.

    Completely greenfield development. Right now there are zero users, and zero workload.


  • Impossible Mission Players - A

    @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Completely greenfield development. Right now there are zero users, and zero workload.

    👍 that always helps. Triggers themselves look good from the perspective of being a light touch history trigger, so since all queries will be new, should be good for making sure the application queries are trigger friendly 😆



  • I'd use GETUTCDATE instead of GETDATE, you don't want DST changes messing with your history ordering. (unless GETDATE has datetimeoffset type, then you should be ok.)



  • @robo2 said in Dr. Blakeyrat's Mean Bean History Table Machine:

    I'd use GETUTCDATE instead of GETDATE, you don't want DST changes messing with your history ordering. (unless GETDATE has datetimeoffset type, then you should be ok.)

    Yeah good idea. I thought about changing the DateTime columns to DateTimeOffset columns, but I'm not sure how well those are supported yet...

    EDIT: it turns out there's no one-liner yet to grab your current DateTimeOffset, you have to do this convoluted thing where you grab the local time, then grab the timezone then munge shit into the DateTimeOffset and basically my suspicions were right: it's not very well-supported at the moment.

    So UTC time it is.



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Fun quirk of creating tables using select into: if the original table has an autonumber, the new table will as well. But for a history table, obviously I don't want an autonumber because it'll prevent me from adding history, how to fix?

    Something with a GUID? Like select newguid, deleted.* from deleted?



  • @sockpuppet7 said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Something with a GUID? Like select newguid, deleted.* from deleted?

    Sentence fragments. Not understanding post. What talking about?


  • kills Dumbledore

    When did SQL Server get create or update? I'm used to having to add a drop if object_id is not null in my proc create scripts


  • Fake News



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    I'm particularly proud of that idiotic moron workaround.

    It's beautiful in a very perverted way, I like it.

    Have you documented its purpose or is this a "it was hard to write, it should be hard to read!" kind of situation? Or a throwaway?



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Fun quirk of creating tables using select into: if the original table has an autonumber, the new table will as well. But for a history table, obviously I don't want an autonumber because it'll prevent me from adding history, how to fix?

    Can you do some kind of casting trick to misdirect the DB? select to_number(to_char( id ) ) (or whatever)? Arithmetic? id * 1.0?


  • kills Dumbledore

    @boomzilla Only if you know the column details I'd have thought.

    Even if all PK columns are just called id, to avoid including the ID unmolested, you'd have to name every other column too. It would be doable with sys.columns but a lot messier than the current solution



  • @boomzilla said in Dr. Blakeyrat's Mean Bean History Table Machine:

    @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Fun quirk of creating tables using select into: if the original table has an autonumber, the new table will as well. But for a history table, obviously I don't want an autonumber because it'll prevent me from adding history, how to fix?

    Can you do some kind of casting trick to misdirect the DB? select to_number(to_char( id ) ) (or whatever)? Arithmetic? id * 1.0?

    But then you'd have to know which columns are identity (or rather, numeric) and which aren't.



  • @maciejasjmj said in Dr. Blakeyrat's Mean Bean History Table Machine:

    But then you'd have to know which columns are identity (or rather, numeric) and which aren't.

    Oh, right forgot about the select * nature of the thing. Well, I assume there's a built in data dictionary set of tables where you could get this sort of information, no?

    PITA, but no one said this stuff was easy.



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    I'm particularly proud of that idiotic moron workaround.

    You're doing something which is wrong at first glance but for a very good reason. It deserves a comment, otherwise someone else who sees this code (or you next year) may optimise out the union.



  • @maciejasjmj said in Dr. Blakeyrat's Mean Bean History Table Machine:

    @boomzilla said in Dr. Blakeyrat's Mean Bean History Table Machine:

    @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Fun quirk of creating tables using select into: if the original table has an autonumber, the new table will as well. But for a history table, obviously I don't want an autonumber because it'll prevent me from adding history, how to fix?

    Can you do some kind of casting trick to misdirect the DB? select to_number(to_char( id ) ) (or whatever)? Arithmetic? id * 1.0?

    But then you'd have to know which columns are identity (or rather, numeric) and which aren't.

    IIRC there are metadata tables in SQL server that describe the table columns. The solution would get a lot uglier, but should work.



  • @sockpuppet7 said in Dr. Blakeyrat's Mean Bean History Table Machine:

    @maciejasjmj said in Dr. Blakeyrat's Mean Bean History Table Machine:

    @boomzilla said in Dr. Blakeyrat's Mean Bean History Table Machine:

    @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Fun quirk of creating tables using select into: if the original table has an autonumber, the new table will as well. But for a history table, obviously I don't want an autonumber because it'll prevent me from adding history, how to fix?

    Can you do some kind of casting trick to misdirect the DB? select to_number(to_char( id ) ) (or whatever)? Arithmetic? id * 1.0?

    But then you'd have to know which columns are identity (or rather, numeric) and which aren't.

    IIRC there are metadata tables in SQL server that describe the table columns. The solution would get a lot uglier, but should work.

    I think Blakey's one is good enough (and creating empty table twins via WHERE 1=0 is a hack anyway, albeit one that's a bit better known).



  • @another_sam said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Have you documented its purpose or is this a "it was hard to write, it should be hard to read!" kind of situation? Or a throwaway?

    I haven't, because it's not part of the actual application. It's just a sproc that makes making history tables easier. I probably should.

    @boomzilla said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Can you do some kind of casting trick to misdirect the DB?

    Probably, but it would require me to know:

    • That the table having a history table made has an identity column
    • The name of that identity column
    • The type of that identity column (the actual data tables are going to use GUIDs, for example)

    Right now, my sproc doesn't need to know any of those things.

    @pleegwat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    You're doing something which is wrong at first glance but for a very good reason. It deserves a comment, otherwise someone else who sees this code (or you next year) may optimise out the union.

    Yeah but, again, it's not part of the product's code. I might add a line or two of comment, but since it'll sit in my network drive, whatever.

    @sockpuppet7 said in Dr. Blakeyrat's Mean Bean History Table Machine:

    IIRC there are metadata tables in SQL server that describe the table columns. The solution would get a lot uglier, but should work.

    If you guys want to write a huge ugly stupid solution, feel free. I'm happy with this as-is.



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    If you guys want to write a huge ugly stupid solution, feel free. I'm happy with this as-is.

    Oh. I guess we were just trying to suggest ways to write high quality software. Apologies.



  • @boomzilla said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Oh. I guess we were just trying to suggest ways to write high quality software. Apologies.

    This isn't software. This is something I busted out in an hour to save me from having to make history tables manually on 15 or so tables.

    And I wouldn't have done that if I'd have believed that my company would spring for the Enterprise edition of SQL Server, which has this functionality built-in. Which would clearly be the correct solution to this problem.



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    Yeah but, again, it's not part of the product's code. I might add a line or two of comment, but since it'll sit in my network drive, whatever.

    If it's an actual one-off, indeed, who cares.



  • @blakeyrat said in Dr. Blakeyrat's Mean Bean History Table Machine:

    I'm particularly proud of that idiotic moron workaround.

    If I put quotes on my wall, this would be one of them.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.