Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1565
Members Online:
3
Guests Online:
14

Total Post History
Posts:
80457
Topics:
18410

7-Day Post History
New Posts:
25
New Topics:
9
Active Topics:
11

Our newest member
EdElliott

Other

FAQ
All times are UTC [ DST ]

Google Ads

Update Trigger

Microsoft SQL Server
Please wait...

Update Trigger

Postby Thirster42 on Thu Jun 04, 2009 9:09 pm

So I have a trigger that i'm supposed to write that when any data in a table is updated the old and new information is inserted into another table. Well the problem is that the table that I have to write the trigger for has 50+ columns on it. is there a better way to write the trigger instead of writting a seperate insert statement for each column?

here's what the destination table looks like, it'll have data from multiple tables in it:

  1. CREATE TABLE [LOG].[DataChanges](
  2.     [PK_dcID] [INT] IDENTITY(1,1) NOT NULL,
  3.     [dcTableName] [NVARCHAR](255) NULL,
  4.     [dcColumnName] [NVARCHAR](25) NULL,
  5.     [dcID] [INT] NULL,
  6.     [dcDataBefore] [NVARCHAR](MAX) NULL,
  7.     [dcDataAfter] [NVARCHAR](MAX) NULL,
  8.     [dcDateChanged] [DATETIME] NULL,
  9.     [dcUser] [NVARCHAR](50) NULL,
  10.  CONSTRAINT [PK_DC] PRIMARY KEY CLUSTERED
  11. (
  12.     [PK_dcID] ASC
  13. )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  14. ) ON [PRIMARY]
  15.  
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Update Trigger

Postby SQLDenis on Thu Jun 04, 2009 9:31 pm

that is what I usually do


you can probably code gen the trigger by looping over the columns of the table and printing out the code and then you paste it into the trigger
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455
LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455
LTD Gold - Rating: 3455LTD Gold - Rating: 3455LTD Gold - Rating: 3455
 
Posts: 21733
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated

Re: Update Trigger

Postby Thirster42 on Thu Jun 04, 2009 9:34 pm

SQLDenis wrote:that is what I usually do


you can probably code gen the trigger by looping over the columns of the table and printing out the code and then you paste it into the trigger


yeah i thought about doing something like that...
Ask three DBAs a question, and you’ll get four different answers.

-Brent Ozar
User avatar
Thirster42
Guru
Guru
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
LTD Silver - Rating: 517LTD Silver - Rating: 517LTD Silver - Rating: 517
 
Posts: 4639
Joined: Tue Nov 11, 2008 5:49 pm
Location: Jefferson City, Mo
Unrated

Re: Update Trigger

Postby Emtucifor on Fri Jun 05, 2009 6:20 am

Send me a private message or use my email address if you have it. I will share code with you I wrote that creates said trigger on any table you want... automatically.

At this time it doesn't support text or image datatypes and if you change primary keys on the table then you have to manually change primary keys on the history table. But otherwise it's pretty polished. I use it in a couple of in-house-developed applications.
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: Update Trigger

Postby Emtucifor on Fri Jun 05, 2009 6:42 am

Oh, and to answer your specific question "is there a better way to write the trigger instead of writing a separate insert statement for each column?"

Yes.

  1. INSERT LOG.DataChanges
  2. SELECT
  3.    TableName = 'TableName',
  4.    ColumnName = CASE ColumnID WHEN 1 THEN 'Column1' WHEN 2 THEN 'Column2' WHEN 3 THEN 'Column3' WHEN 4 THEN 'Column4' END
  5.    ID = Key1,
  6.    ID2 = Key2,
  7.    ID3 = Key3,
  8.    DataBefore = CASE ColumnID WHEN 1 THEN I.Column1 WHEN 2 THEN I.Column2 WHEN 3 THEN I.Column3 WHEN 4 THEN I.Column4 END,
  9.    DataAfter = CASE ColumnID WHEN 1 THEN D.Column1 WHEN 2 THEN D.Column2 WHEN 3 THEN D.Column3 WHEN 4 THEN D.Column4 END,
  10.    DateChange = GETDATE(),
  11.    USER = WhateverFunctionYouAreUsingForThis
  12. FROM
  13.    Inserted I
  14.    FULL JOIN Deleted D ON I.Key1 = D.Key1 AND I.Key2 = D.Key2
  15.    CROSS JOIN (
  16.       SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
  17.    ) X (ColumnID)
  18.  
  19.  

However, I do not recommend this way of storing history. You will find that breaking up the columns means that querying the table for old values will become nearly impossible. It won't be too super hard to display in a web page a history of changes, but doing any kind of query to show where someone changed something will become extremely hard.

Is ID supposed to be the primary key of the table? Do all the tables you want history on have only a single integer key column? Will it be that way forever? No other data types are keys?

So my first suggestion is, if you insist on putting all the data in a single table, to store the column data as XML. SQL Server has all sorts of built-in utilities for turning rowsets into XML and vice versa. You have some syntax to learn, but getting the data out will be far easier than splitting up each table into columns.

Second, you don't have a TableSchema column. That's important, if only because it's embarrassing to write code that breaks the moment someone does something pretty normal like create a table in another schema and wants history on it.

Third, you could use a separate schema for your history tables, so why not just have a separate table for each data table you want to track? My system doesn't work this way but could be easily modified. It follows the scheme schema.table -> schema.tablehistory. But schema.table -> hist.schema_table would be just as easy. Or any other plan you come up with. I don't recommend putting the history tables into another database because you generally would want backups and restores to keep the data together. Unless your requirements are something different... in which case be careful about taking down your production database because someone thought the history database wasn't critical to its operation.

Last, I recommend storing history on insert, update, and delete, rather than keeping the current values in the table and only old values in the history. Again, you will find that when it comes time to examine history, it is a royal pain in the nether parts to add that current-value-row when you just could have had it in the table at such a low cost of just one more row.

Whatever parts of my advice you take, please do take seriously the difficulties you can encounter in trying to query history. I encourage you to actually build some real queries to pull back data from your log table(s) before you spend a lot of time investing in a particular architecture. Once you see how hard it is to do reasonable queries with the column-split thing I'm confident you'll want an alternative...

Erik
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated