Cliff Buckley

Self proclaimed Data Geek
posts - 3 , comments - 5 , trackbacks - 0

Monday, August 10, 2009

An Alternative to Triggers

EDIT: I of course am a dork and put the wrong title for this. It's corrected.

I came across a scenario that caused me to think of alternatives to triggers. The requirement was to keep history in a separate table than the current record. It was implemented as a trigger, but with the nature of triggers, we wanted to examine some alternatives. What I came up with was taking advantage of the OUTPUT clause on Update to insert the original values into the history table. Below is an overly simplified example of how to accomplish this.


IF OBJECT_ID('tempdb..#Current') IS NOT NULL
   DROP TABLE #Current

IF OBJECT_ID('tempdb..#Hist') IS NOT NULL
    DROP TABLE #Hist

CREATE TABLE #Current(ID int IDENTITY(1,1) , Key varchar(20),  Val varchar(255))
CREATE TABLE #Hist(HistID int IDENTITY(1,1) ,OriginalID int, Key varchar(20),  Val varchar(255))

INSERT INTO #Current(Key,Val) VALUES('Root Dir','X:\Files')
INSERT INTO #Current(Key,Val) VALUES('Temp Dir','C:\')

SELECT ID,Key,Val FROM #Current

-- now we realise that C:\ is not the correct value for the temp dir setting, we want to update it but track history...

UPDATE #Current
SET Val = 'C:\Temp\'
OUTPUT DELETED.ID,DELETED.Key,DELETED.Val INTO #Hist(OriginalId,Key,Val)
WHERE Key = 'Temp Dir'

SELECT ID, Key, Val FROM #Current
SELECT HistId,OriginalId,Key,Val FROM #Hist


EDIT: Corrected the format.

Posted On Monday, August 10, 2009 9:26 PM | Comments (0) | Filed Under [ MS SQL Server ]

Monday, April 20, 2009

SQL Server 2010

Looks like I'll get a new version to play with soon. According to this article, SQL 2010 is coming out with a preview release (IE extreme beta I would guess) the first part of 2009. 2008 just came out and I am working on my 2005 certs. It's nice that we are getting a new release so regularly now (or apparantly so regularly) but those who have 2008, there will need to be some real strong reasons to upgrade I would imagine. We'll see how it looks when it comes out. I have to admit, I am looking forward to see what they are doing with this Gemeni tool.

Here is the article for those of you interested: Microsoft SQL Server 2010

Posted On Monday, April 20, 2009 4:07 PM | Comments (5) | Filed Under [ MS SQL Server ]

Thursday, November 6, 2008

MDX Studio

I just discovered this fabulous tool today. If you use MDX often, take a look and download the latest version. Very well put together and it's still in beta.

http://www.mosha.com/msolap/mdxstudio.htm

Posted On Thursday, November 6, 2008 9:11 PM | Comments (0) | Filed Under [ SSAS ]

Powered by: