Clr Trigger for Insert

Question

Clr Trigger for Insert

anupamabr

SSC Eights!

Points: 930
More actions
October 31, 2008 at 11:53 am

#214800

Hi
I need to use Clr trigger for insert command
My code is as below
I am using SQL server 2005 and VS 2008.... but after running this code i didnt get the result as i expexted it shows the result as no row is effected ...Please help me guys
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.IO;
//using System.Transactions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the attribute line
[Microsoft.SqlServer.Server.SqlTrigger(Name = @"firstTrigger", Target = "dbo.CrossSell", Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerContext triggContext = SqlContext.TriggerContext;
//string st = triggContext.EventData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml(st);
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter();
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
command = new SqlCommand(@"SELECT * FROM INSERTED;",
connection);
SqlDataAdapter da = new SqlDataAdapter("select*from inserted ",connection);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(writer,XmlWriteMode.WriteSchema,false);
string xmlFromDataTable = writer.ToString();
reader = command.ExecuteReader();
reader.Read();
CrossSellId = (Guid)reader[0];
int_id = (int)reader[1];
ProductId = (Guid)reader[2];
CrossSellingId = (Guid)reader[3];
reader.Close();
////// command = new SqlCommand(
//////"INSERT into CrossSell (CrossSellId, int_id,ProductId,CrossSellingId) " +
//////"VALUES (@CrossSellId, @int_id,@ProductId,@CrossSellingId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId + @"," + CrossSellingId + @");",
connection);
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
pipe.Send(xmlFromDataTable);
//pipe.Send("CrossSell inserted!");
//connection.Open();
//da.Fill(dt);
// connection.Close();
}
break;
}

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

I don't see anywhere in your code that you need CLR to do this. A Simple TSQL Trigger would do this much better.

anupamabr SSC Eights! Points: 930 More actions · Answer 2

Do u knw y the insert command is not working there?what is wrong with my code?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I thought you needed to fire the execution off from the pipe side, and not the "old" way?

As in -

SQLContext.Pipe.ExecuteAndSend(command);

and not

command.ExecuteNonQuery();

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Jonathan Kehayias (10/31/2008)
I don't see anywhere in your code that you need CLR to do this. A Simple TSQL Trigger would do this much better.

Agreed. Never mind a whole heck of a lot faster, too:)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

anupamabr SSC Eights! Points: 930 More actions · Answer 5

thanks for your quick response ..eventhogh i changed the code it didnt work

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

anupamabr (10/31/2008)
thanks for your quick response ..eventhogh i changed the code it didnt work

Do you mean that you changed it to T-SQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 7

Jeff Moden (10/31/2008)
Do you mean that you changed it to T-SQL?

If you did and you still have problems, can you post your TSQL Trigger code. If you didn't, can you post your table DDL Statement?

anupamabr SSC Eights! Points: 930 More actions · Answer 8

I am using Visual Studio 2008 for Clr trigger insert....

I created a CLR trigger for insert command by above code...

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlTypes;

using System.Data.SqlClient;

using Microsoft.SqlServer.Server;

using System.Xml;

using System.IO;

//using System.Transactions;

public partial class Triggers

{

// Enter existing table or view for the target and uncomment the attribute line

[Microsoft.SqlServer.Server.SqlTrigger(Name = @"firstTrigger", Target = "dbo.CrossSell", Event = "FOR Insert")]

public static void firstTrigger()

{

Guid CrossSellId;

int int_id;

Guid ProductId;

Guid CrossSellingId;

SqlCommand command;

SqlTriggerContext triggContext = SqlContext.TriggerContext;

//string st = triggContext.EventData.Value;

// XmlDocument xmlDoc = new XmlDocument();

//xmlDoc.LoadXml(st);

SqlPipe pipe = SqlContext.Pipe;

SqlDataReader reader;

// DataTable dt = new DataTable();

//SqlDataAdapter da = new SqlDataAdapter();

switch (triggContext.TriggerAction)

{

case TriggerAction.Insert:

// Retrieve the connection that the trigger is using

using (SqlConnection connection

= new SqlConnection(@"context connection=true"))

{

connection.Open();

command = new SqlCommand(@"SELECT * FROM INSERTED;",

connection);

SqlDataAdapter da = new SqlDataAdapter("select*from inserted ",connection);

DataTable dt = new DataTable();

da.Fill(dt);

StringWriter writer = new StringWriter();

dt.WriteXml(writer,XmlWriteMode.WriteSchema,false);

string xmlFromDataTable = writer.ToString();

reader = command.ExecuteReader();

reader.Read();

CrossSellId = (Guid)reader[0];

int_id = (int)reader[1];

ProductId = (Guid)reader[2];

CrossSellingId = (Guid)reader[3];

reader.Close();

////// command = new SqlCommand(

//////"INSERT into CrossSell (CrossSellId, int_id,ProductId,CrossSellingId) " +

//////"VALUES (@CrossSellId, @int_id,@ProductId,@CrossSellingId)", connection);

command = new SqlCommand(

@"INSERT [dbo].[CrossSell] VALUES ("

+ CrossSellId + @", " + int_id + @"," + ProductId + @"," + CrossSellingId + @");",

connection);

pipe.Send(command.CommandText);

SqlContext.Pipe.ExecuteAndSend(command);

//SQLContext.Pipe.ExecuteAndSend(command);

//command.ExecuteNonQuery();

pipe.Send(xmlFromDataTable);

//pipe.Send("CrossSell inserted!");

//connection.Open();

//da.Fill(dt);

// connection.Close();

}

break;

}

..................................................................................................................

After this i need to update my ProductBase table

DECLARE @ProductBase TABLE (ID int IDENTITY(1,1), CrossSell xml)

INSERT INTO @ProductBase

DEFAULT VALUES

SELECT * FROM @ProductBase

Update @ProductBase

set CrossSell = ' '

where CrossSell IS NULL

SELECT * FROM @ProductBase

then it shows the updated result...

This what i did ....

but after debugging the clr trigger it shows no rows are effected i dont kknw what is the problem with it..i am new to this...thanks in advance for your help