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:
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.