Geek fun with DDL Triggers

This positing first appeared in an earlier blog that I have now replaced with this current opus. It's a bit more technical that most of what I am putting in this blog, but I'm still a database geek at heart and I wanted to move it over before I wrapped up the earlier blog. I hope no-one takes offence at this bit of self plagiarism

With all the big changes in SQL Server 2005 I've been focusing on some small but significant changes. The first of these is the Data Definition Language (DDL) triggers.

In a nutshell, SQL Server 2005 will allow you to set a trigger at the database level for a myriad of DDL actions (consult your friendly neighborhood BooksOnline for the full list).

I've chosen an particular application of this functionality here, one that solves a problem I had with earlier SQL versions -- table management.

The problem is like this: suppose you, as DBA, notice that a table has been deleted. You go into the SQL Server logs but find no reference to the dropped table. Nor can you find out who dropped it and when.

Now you can.

In a test database, I created a log table in my test DB that looked like this:

CREATE TABLE [dbo].[DDL_Log]
( [id] [int] IDENTITY(1,1) NOT NULL,
[DDL_Event] [nvarchar](100) NOT NULL,
[TSQL_code] [nvarchar](2000) NOT NULL,
[ExecutedBy] [nvarchar](100) NOT NULL DEFAULT current_user,
[Action_date] [datetime] NULL DEFAULT (getdate()))
ON [PRIMARY];

I then created a DDL trigger on the database using the following syntax:

CREATE TRIGGER [log_table_actions]
ON DATABASE FOR DROP_TABLE, ALTER_TABLE, CREATE_TABLE
AS
BEGIN

DECLARE @data XMLSET @data = EVENTDATA()
INSERT dbo.ddl_log (DDL_Event, TSQL, ExecutedBy, Action_Date)
VALUES (@data.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar (2000)'), CONVERT(nvarchar(100), (CURRENT_USER)), GETDATE())
End;

A few thing will jump out immediately. The first is that the trigger is created "ON DATABASE" rather than on a table and secondly, that the trigger is create on all CREATE, ALTER and DROP TABLE statements. Finally it uses the eventdata() function to query information on the DDL statement that called the trigger.

Please note that this function returns XML, so you'll need to capture the output inside and XML typed variable, and you need to query elements inside the element to get the data. In this case, I've returned the event type and the full TSQL code for the DDL statement.I'm still looking for the full schema for the eventdata() xml document. But that will be a post for another day.

Web Applications: Death of the desktop app? (Hint: It depends on who you ask)

Lately, I have been noticing more and more commentators writing about, and podcasting about web-based applications making the desktop applications obsolete. The two most obvious examples cited are Salesforce.com and many of the Google applications like Gmail and the Google Docs and Spreadsheet. Certainly, anyone watching what Google is up to will get this sense.

The idea is a sound one: write applications using web-based technology, and make them available on the Internet, where, hypothetically, they will run in any web-browser, regardless of what operating system is running in the background. Anyone who tries to find desktop apps to run on both Windows and a Mac will appreciate the thought behind this level of abstraction.


Does this trend spell the end of desktop applications? For my money the answer is, as is so often the case, probably, but not now. It has a lot of promise, but there are currently, some major road blocks to adoption.

Let's look at three major ones:

1. Connectivity

As I type this blog entry I am using a web-based application which is, by most accounts, fairly robust. It allows me to format text, add hyperlinks (as you see above) and even finds my more egregious typos. It even has a auto save function which has saved me a few times. To use it, all I had to do was connect to a web site. Out of curiosity, I started this post on a Mac at the office and now finish it on my home PC.

However, I would have preferred to finish this post on the my train ride home, but the lack of connectivity meant I was left working on spreadsheet in Excel that was installed and saved on my hard drive.

Lack of universal connectivity is the first road-block to the complete adoption of online applications. There are times when we are just not able to connect. Unfortunately, often these periods of non-connectivity, like on long flights, or commuter train rides, are the times when we are most likely to want to to work.

2. Technical incompatibility

The second road block is the the technology itself. Microsoft, which admittedly has a lot to lose by the global adoption of web-based applications like Google spreadsheet, has been leveraging the .NET platform to drive the creation of web applications. It has also built a back-end infrastructure to support web apps. We are currently looking for a document management solution that both the PC and Mac users can use to collaborate on documents. I was doing some early testing on SharePoint portal server and thought I had a winner. I was almost right. The Macs, using FireFox could access the portal and browse to the document library, however, they were unable to check documents in and out. This functionality, of course, worked fine with the PC running Internet Explorer, but defeats its entire purpose on the Mac.

Now, it's easy to adopt the Microsoft is evil stance, however, as a developer of web-based applications, I know how hard it is to make things, even as simple as an HTML email, render properly across different platforms. The OS makers have a vested interest in keeping their platforms distinct and I don't see this changing any time soon. They also tune their backend platforms to work most efficiently with their own technology.

3. Data Security

One final barrier to full adoption of web-based applications is one that I also struggle with on both sides -- the storage of data. The apps live on a web server somewhere in the Cloud that is the Internet. In many cases, your data will live in a database or file store connected to that web server, or so you hope.

The truth is, in many cases, you don't actually know where your data is, and who has access to it. Most legitimate vendors, including my company, go to great lengths to secure data and set an appropriate privacy and security policies, but the truth is, once its out of your network, you are at the the mercy of someone else's systems and system administrators. So trust in your vendor becomes key.

I think the trend of moving applications online will continue. The rise of Software as a Service applications like Salesforce.com will push this field. Also, as Wi-Fi technology becomes more pervasive, and workforces become more remote and less tied to a cubicle, the reach of online applications will increase. In the meantime, find a vendor that you trust and look at the business needs driving the decision, but don't be afraid to make the leap.

Microsoft Surface: Not close to home, but cool for business

Last week Microsoft release a preview of Microsoft Surface. I been watching some cool demos of surface and gestural interface computing systems and prototypes at sites like Engaget and YouTube for a while, but it looks like MS has something that is almost ready for prime time.

What MS is showing is a 30 inch table like screen that can interact with devices like the Zune (which as a Canadian still does not exist in my world) and a Mobile device.

I don't see this as a home item yet (although I'd love one right now), however, as a tool for marketers this is huge.

Take a look at the three flash videos on the Microsoft site (http://www.microsoft.com/surface).

My head is already filling with ideas for retail applications of this technology to their online initiatives. Now I need to see who I can plead with at Microsoft to get me early access to the technology. I'll keep you posted.