DDL triggers allow you to write SQL code that executes whenever a DDL (data definition language) event occurs. This means you can capture, and handle, any event that modifies for instance stored procedures, views, DML trigger, etc. In this post, I’m going to set up a simple version control process using DDL triggers.
If you’re working on a database with other developers, you’ve probably been there – you’re modifying a stored procedure using “ALTER PROCEDURE”, but after a while (or, at worst, much later) you find out another developer has been working on the same code, and now you’ve mixed up your versions.
The solution to this would be some kind of functionality where you could check out a database object, so nobody else could change it while you’re working on it. When you’re done, you could check it back in, and as a bonus, you’d have an audit trail of sorts, over who made what changes.
How DDL triggers work
A DDL trigger works just like an “ordinary” (DML) trigger, in the sense that you determine one or multiple conditions upon which it fires. When it does fire, it executes the code you’ve programmed it to run.
Because the trigger runs atomically, as a part of the DML or DDL statement that fired it, if you place a ROLLBACK TRANSACTION statement in the trigger, not only does it roll back what the trigger has performed, but also the action of the statement that started the trigger. You’ll see this put to use further on.
In a regular DML trigger, you can see which modifications have taken place using the special inserted and deleted tables. But because DDL triggers do not work with data modifications but rather DDL modifications, the modification that triggered the DDL trigger is stored in an XML document instead. This XML document can be obtained using the EVENTDATA() function. Here’s a sample:
<EVENT_INSTANCE>
<EventType>CREATE_TYPE</EventType>
<PostTime>2012-03-29T13:52:01.197</PostTime>
<SPID>53</SPID>
<ServerName>DERRICKV-MSFT</ServerName>
<LoginName>REDMOND\derrickv</LoginName>
<UserName>dbo</UserName>
<DatabaseName>AdventureWorks2008R2</DatabaseName>
<SchemaName>dbo</SchemaName>
<ObjectName>Phone</ObjectName>
<ObjectType>TYPE</ObjectType>
<TSQLCommand>
<SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
<CommandText>CREATE TYPE [Phone] FROM nvarchar(25) NULL;
</CommandText>
</TSQLCommand>
</EVENT_INSTANCE>
If you’re not familiar with working with XML data in SQL Server, have a look at the XML tutorial article I posted a while back.
Setting up a version control table
To set up our version control “repository”, we’ll need a table in which to store what objects have been checked in/out, what state they are in, which user has checked the object out, along with a descriptive text and the source code of the committed change.
CREATE TABLE dbo.object_checkout_state ( [object_id] int NOT NULL, --- The object ID [schema_name] sysname NOT NULL, --- Schema [object_name] sysname NOT NULL, --- Object name [user] varchar(255) NOT NULL, --- User login name checked_out datetime NOT NULL, --- Date/time checked out checked_in datetime NULL, --- Date/time checked in [description] varchar(max) NULL, --- Text narrative varchar(max) NULL, --- Checked-in SQL code PRIMARY KEY CLUSTERED ([object_id], checked_out) );
The idea is to track all checkins and checkouts. Because an object can be checked out multiple times and then checked back in, the primary key is a composite key consisting of both object_id and checked_out.
Security hint: If you’re going to use this in anything that even remotely resembles a production environment, make sure to set sufficiently tight permissions on this table! It will contain the source code of any object that gets checked in. Also, you won’t want users to change or delete checkins that aren’t theirs.
Procedures for checking in and out
Next up are two stored procedures, one used to check out an object and the other to check the object back in. First, the check-out procedure:
CREATE PROCEDURE dbo.sp_checkout
@object_id int
AS
SET NOCOUNT ON;
DECLARE @msg varchar(max);
--- Check that the user has provided a valid object_id
IF (@object_id IS NULL OR OBJECT_NAME(@object_id) IS NULL)
SET @msg='@object_id is invalid.';
--- Check that the object isn't already checked-out
IF (@msg IS NULL)
SELECT TOP 1 @msg='Object is checked out by '+[user]+
' as of '+CONVERT(varchar, GETDATE(), 121)+'.'
FROM dbo.object_checkout_state
WHERE [object_id]=@object_id AND checked_in IS NULL;
--- If there's anything wrong, stop here, display an error and exit.
IF (@msg IS NOT NULL) BEGIN;
RAISERROR(@msg, 16, 1);
RETURN;
END;
--- Add a checkout to the version control table:
INSERT INTO dbo.object_checkout_state ([object_id], [schema_name],
[object_name], [user], checked_out)
SELECT o.[object_id], s.[name] AS [schema_name],
o.[name] AS [object_name], SUSER_SNAME(), GETDATE()
FROM sys.objects AS o
INNER JOIN sys.schemas AS s ON o.[schema_id]=s.[schema_id]
WHERE o.[object_id]=@object_id;
--- Done, report back to user with success message.
IF (@@ROWCOUNT!=0)
PRINT 'Checked out '+OBJECT_NAME(@object_id)+'.';
The check-in procedure looks similar:
CREATE PROCEDURE dbo.sp_checkin @object_id int, @description varchar(max)=NULL AS DECLARE @msg varchar(max); --- Already checked out by another user? SELECT TOP 1 @msg='Object is checked out by '+[user]+ ' as of '+CONVERT(varchar, GETDATE(), 121)+'.' FROM dbo.object_checkout_state WHERE [object_id]=@object_id AND checked_in IS NULL AND [user]!=SUSER_SNAME(); --- Not checked out in the first place? IF (@msg IS NULL AND @object_id NOT IN ( SELECT [object_id] FROM dbo.object_checkout_state WHERE checked_in IS NULL)) SET @msg='Object is not checked out.'; --- If there's anything wrong, report and exit: IF (@msg IS NOT NULL) BEGIN; RAISERROR(@msg, 16, 1); RETURN; END; --- Update the version control table: UPDATE s SET s.checked_in=GETDATE(), s.=m.[definition], s.[description]=@description FROM dbo.object_checkout_state AS s --- LEFT JOIN is used, beacause not all objects have --- SQL code in the sys.sql_modules table (for instance --- tables, their indexes, etc). LEFT JOIN sys.sql_modules AS m ON s.[object_id]=m.[object_id] WHERE s.[object_id]=@object_id AND s.checked_in IS NULL; --- And report success to user: PRINT 'Checked in '+OBJECT_NAME(@object_id)+'.';
With the basics set up, now all we need to do is to make sure that nobody else can meddle with the objects that we’ve checked out. This is where a DDL trigger comes into play. It checks all the DDL statements on all database objects against the version control table.
The DDL trigger
ALTER TRIGGER checkoutTrigger ON DATABASE --- DDL_DATABASE_LEVEL_EVENTS covers all DDL events within a --- database. You could change this to a list of specific events --- that you wish to cover. FOR DDL_DATABASE_LEVEL_EVENTS AS SET NOCOUNT ON; DECLARE @event_xml xml, --- EVENTDATA() output @schema_name sysname, --- Schema name of object @object_name sysname, --- Object name @object_id int, --- Object ID @checked_out_by varchar(255), --- User that has checked object out @msg varchar(max); --- Error message --- Collect EVENTDATA() and parse it into @schema_name and @object_name SET @event_xml = EVENTDATA(); SET @schema_name = @event_xml.value( '(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'); SET @object_name = @event_xml.value( '(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'); --- Look up @object_id. This won't work if the DDL statement has --- dropped the object, because the trigger executes after the fact! SET @object_id=OBJECT_ID('['+@schema_name+'].['+@object_name+']'); --- Look up who has checked out this object, if anyone: SELECT TOP 1 @checked_out_by=[user] FROM dbo.object_checkout_state WHERE ([object_id]=@object_id OR @object_id IS NULL AND [schema_name]=@schema_name AND [object_name]=@object_name) AND checked_in IS NULL; --- If someone has checked this object out, and it's not the --- current user, roll back the transaction (and implicitly the --- original DDL statement) and return an error message. IF (@checked_out_by!=SUSER_SNAME()) BEGIN; SET @msg='Object is checked out by '+@checked_out_by+ ' and cannot be updated.'; RAISERROR(@msg, 16, 1); ROLLBACK TRANSACTION; END;
Testing the solution
Let’s create a dummy SP and table to test the version control logic:
CREATE TABLE dbo.test ( a int NOT NULL ); GO CREATE PROCEDURE dbo.sp_test AS SELECT a FROM dbo.test; GO
.. and check them out to us:
DECLARE @object_id int=OBJECT_ID('dbo.test');
EXECUTE dbo.sp_checkout @object_id;
SET @object_id=OBJECT_ID('dbo.sp_test');
EXECUTE dbo.sp_checkout @object_id;
Then, let’s try some modifications:
ALTER TABLE dbo.test ADD b int NULL; GO ALTER PROCEDURE dbo.sp_test AS SELECT a, b FROM dbo.test; GO
So far, all is well. Now, log in with a different user, and try some modifications:
ALTER TABLE dbo.test ADD c int NULL; Msg 50000, Level 16, State 1, Procedure checkoutTrigger, Line 31 Object is checked out by xyz and cannot be updated. Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted.
The same happens if you try to change the stored procedure:
ALTER PROCEDURE dbo.sp_test AS SELECT a, b FROM dbo.test WHERE 1=1; GO Msg 50000, Level 16, State 1, Procedure checkoutTrigger, Line 31 Object is checked out by xyz and cannot be updated. Msg 3609, Level 16, State 2, Procedure sp_test, Line 3 The transaction ended in the trigger. The batch has been aborted.
Now, go back to the original user (the one you used to check these objects in) and check in your modifications.
DECLARE @object_id int=OBJECT_ID('dbo.test');
EXECUTE dbo.sp_checkin @object_id, @description='Added column b.';
SET @object_id=OBJECT_ID('dbo.sp_test');
EXECUTE dbo.sp_checkin @object_id, @description='Added column b';
Obviously, this is a very primitive form of version control. It doesn’t have any support for diffs, merges, blames, and other features that full-blown version control systems have, but it does provide an easy way to keep other people from inadvertedly wrecking your code-in-progress.
Enjoy!