Submitted by randy on Wed, 03/30/2011 - 11:08

I'm working on another SQL Server 2008 and Drupal 7 project right now.  This particular project has a great deal of stored procedures and triggers in it to massage extrenal data to the right format for display in Drupal.

Doing Drupal/PHP development with SQL Server 2008 is easy using the 2008 Express (free) edition for those of us who don't use SQL Server regularily as a database on our development machines.  However, there is one small problem I ran in to on this current project:   SQL 2008 Express does not have any UI mechanisms in the SQL Management Studio to allow you to manage triggers!

The UI has been pared down as this is a free database engine from Microsoft after all.  Rightfully so -- if you want to have all of the features and benefits of SQL Server, you should buy a legitimate license for it (You can't run SQL Express in a production environment anyways).

However for development (especially for my current project), this is an issue that needed to be overcome. In this particular instance, I couldn't use the customer's environment to do development directly on -- just too cumbersome over a vpn without any PHP Development tools loaded in their environment.  

So the following few Transact-SQL statements are incredibly helpful for those of us using SQL 2008 Express for trigger management:

To see a listing of all of the triggers in your database:

SELECT * FROM sys.triggers

 

To delete/drop a specific trigger:

DROP TRIGGER triggername 

 

To see the actual textual data that makes up your trigger:

sp_helptext 'triggername'

 

 Finally, here's a great little stored procedure I put together that produces text output in the SQL management studio to help you cleanly get the text out of an existing trigger:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE _getTriggerCode
	@triggerName varchar(100)
AS
BEGIN
	DECLARE @txt varchar(500) -- used to store the row data we are picking off
	SET NOCOUNT ON;
	CREATE TABLE #tmp
	(
	txt varchar(500)
	)
	INSERT #tmp EXEC sp_helptext @triggerName
	
	--now define a simple cursor to iterate thru the results
	DECLARE txtCursor CURSOR FOR
	SELECT txt FROM #tmp 
	
	OPEN txtCursor
	FETCH NEXT FROM txtCursor INTO @txt
	
	WHILE @@FETCH_STATUS = 0   
	BEGIN   
    print @txt
    FETCH NEXT FROM txtCursor INTO @txt
	END   
	
	CLOSE txtCursor   
	DEALLOCATE txtCursor
	DROP TABLE #tmp
END

Use the stored procedure like this:

DECLARE @RC int
DECLARE @triggerName varchar(100)
set @triggerName = 'triggername'  
EXECUTE @RC = _getTriggerCode
   @triggerName
GO

Then just simply look at the Messages tab output in SQL Server Management Studio and voila, you have your trigger code.

General Tags