Altering a trigger in Sql Server 2005

Question

I need to alter a trigger in sql server 2005 and I want to do it using a table adapter and a sql statement, not a stored proc. I know I could run this in the query browser and execute it but I need to deploy it across several databases and use a table adapter in the update. Is this possible?

Doing Add Query -> Update -> pasting the below code -> Query Builder to see if it parses

print("USE [DataBaseName]
GO
/****** Object:  Trigger [dbo].[UpdateCurrentAddress]    Script Date: 10/30/2008 14:47:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateCurrentAddress] ON [dbo].[PreviousAddresses] 
FOR INSERT, UPDATE, DELETE
AS
-- Check to see if there was an insert/update or a deletion.
IF (SELECT COUNT(*) FROM inserted) >= 1
BEGIN
    IF (SELECT CountryID FROM inserted) <> 181

...moar...");

error... The USE [DataBaseName] SQL construct or statement is not supported.

and if i remove the top part and start at just ALTER TRIGGER

The ALTER TRIGGER SQL construct or statement is not supported.

I'm still fairly new to this and would not be surprised that either I'm either going about this the wrong way and/or it's impossible to do without a stored proc.

EDIT: Yeah I'm doing this in C#.

thanks. I can do it that way starting at ALTER TRIGGER, this will get the job done.

Answer 1

The TableAdapter is expecting a resultset, not an actual query. To do this successfully you will need to use the SqlCommand object to actually peform your update.

If you have not used one before it is quite simple, first you declare your connection, then you create your command using the connection. Once the command is created set the commandtext equal to your script, and then you can call the ExecuteNonQuery() method to run the script after opening the connection. If you say what language you are using, I can try to provide an example.

Edit

Here is a C# example, quick and dirty but it gets the point across. NOTE, done from memory, but should be correct.

using(SqlConnection oConnection = new SqlConnection("Yourconnectionhere"))
using(SqlCommand oCommand = new SqlCommand(oConnection))
{
    //Configure the command object
    oCommand.CommandText = "Your script here";

    //Open connectin and run script
    oConnection.Open();
    oCommand.ExecuteNonQuery();
    oConnection.Close();
}