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.Sql Types;
using System.Data.Sql Client;
using Microsoft.SqlSe rver.Server;
using System.Xml;
using System.IO;
//using System.Transact ions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlSe rver.Server.Sql Trigger(Name = @"firstTrigger" ,
Target = "dbo.CrossSell" , Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerConte xt triggContext = SqlContext.Trig gerContext;
//string st = triggContext.Ev entData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml( st);
SqlPipe pipe = SqlContext.Pipe ;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter( );
switch (triggContext.T riggerAction)
{
case TriggerAction.I nsert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@ "context connection=true "))
{
connection.Open ();
command = new SqlCommand(@"SE LECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter( "select*fro m inserted ",connectio n);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(wri ter,XmlWriteMod e.WriteSchema,f alse);
string xmlFromDataTabl e = writer.ToString ();
reader = command.Execute Reader();
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,ProductI d,CrossSellingI d) " +
//////"VALUES (@CrossSellId,
@int_id,@Produc tId,@CrossSelli ngId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(comma nd.CommandText) ;
command.Execute NonQuery();
pipe.Send(xmlFr omDataTable);
//pipe.Send("Cros sSell inserted!");
//connection.Open ();
//da.Fill(dt);
//
connection.Clos e();
}
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
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.Sql Types;
using System.Data.Sql Client;
using Microsoft.SqlSe rver.Server;
using System.Xml;
using System.IO;
//using System.Transact ions;
public partial class Triggers
{
// Enter existing table or view for the target and uncomment the
attribute line
[Microsoft.SqlSe rver.Server.Sql Trigger(Name = @"firstTrigger" ,
Target = "dbo.CrossSell" , Event = "FOR Insert")]
public static void firstTrigger()
{
Guid CrossSellId;
int int_id;
Guid ProductId;
Guid CrossSellingId;
SqlCommand command;
SqlTriggerConte xt triggContext = SqlContext.Trig gerContext;
//string st = triggContext.Ev entData.Value;
// XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.LoadXml( st);
SqlPipe pipe = SqlContext.Pipe ;
SqlDataReader reader;
// DataTable dt = new DataTable();
//SqlDataAdapter da = new SqlDataAdapter( );
switch (triggContext.T riggerAction)
{
case TriggerAction.I nsert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@ "context connection=true "))
{
connection.Open ();
command = new SqlCommand(@"SE LECT * FROM
INSERTED;",
connection);
SqlDataAdapter da = new
SqlDataAdapter( "select*fro m inserted ",connectio n);
DataTable dt = new DataTable();
da.Fill(dt);
StringWriter writer = new StringWriter();
dt.WriteXml(wri ter,XmlWriteMod e.WriteSchema,f alse);
string xmlFromDataTabl e = writer.ToString ();
reader = command.Execute Reader();
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,ProductI d,CrossSellingI d) " +
//////"VALUES (@CrossSellId,
@int_id,@Produc tId,@CrossSelli ngId)", connection);
command = new SqlCommand(
@"INSERT [dbo].[CrossSell] VALUES ("
+ CrossSellId + @", " + int_id + @"," + ProductId +
@"," + CrossSellingId + @");",
connection);
pipe.Send(comma nd.CommandText) ;
command.Execute NonQuery();
pipe.Send(xmlFr omDataTable);
//pipe.Send("Cros sSell inserted!");
//connection.Open ();
//da.Fill(dt);
//
connection.Clos e();
}
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
Comment