Create and Execute Trigger in C#

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

.NET Inside SQL Server (2005)

Create and Execute Trigger in C#

Author

Topic

swetsri
Starting Member

1 Post

Posted - 2008-05-31 : 13:59:39

Hi all
I've Created a Trigger statement and tried to execute this using ExecuteNonQuery.but it shows the following error

Incorrect syntax near 'GO'.
'CREATE TRIGGER' must be the first statement in a query batch.

if i start with Create Trigger statement it show "Incorrect Syntax near Create Trigger".

the following is the trigger statement which i've generated in C#
Can anyone help me?

thanks in advance
sri

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'SampleTrigger' AND type = 'TR')
DROP TRIGGER SampleTrigger
GO
CREATE TRIGGER SampleTrigger
ON dbo.sample
AFTER INSERT
AS begin
SET NOCOUNT ON
DECLARE @RID as int
DECLARE @email AS nvarchar(50)
SELECT @email= i.email from inserted i
DECLARE @Name AS nvarchar(50)
SELECT @Name= i.Name from inserted i
DECLARE @Address AS nvarchar(50)
SELECT @Address= i.Address from inserted i
insert into Register(ServerName,DatabaseName,TableName) values('Sample','SamDatabase','SamTable')
SELECT @RID = @@Identity
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'Name',@Name)
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'Address',@Address)
insert into TableFields(RID,FieldName,FieldValue) values(@RID ,'email',@email)
end

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-05-31 : 16:50:12

GO is not a keyword in t-sql. you have to break this up into two statements to use SqlCommand.ExecuteNonQuery()

Another alternative is to use the SMO class ServerConnection.ExecuteNonQuery() to execute it, because it *does* recognize the GO keyword.

See: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.common.serverconnection.executenonquery.aspx

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources