ClrTrigger.HelpMe("Get Table Name?", Please)

So, I've read some past articles that indciate there's no easy way of knowin
g
within a CLR trigger what table the trigger is acting against?!
I read there are 2 possible solutions:
1) Add a String argument to the CLR Trigger to pass the table name. Then
Create a user defined function for each table that will have this trigger
registered on.
or
2) Use the dm_tran_locks to retrieve the name of the table
Is this still the case?Hi Trex,
Welcome the MSDN newsgroup.
As for the SQL CLR managed trigger, actually it is designed for binding to
a certain target table. that's why at runtime the SQLTriggerContext class
dosn't contain any database/tablename info...
Also, is the article you mentioned the below one?
#SQL Server 2005: CLR Integration
http://blogs.msdn.com/sqlclr/archiv.../21/495438.aspx
I think the method mentioned there such as using an additional string input
parameter to supply table name may be a workaround if you do need to get
the bound table's name in the CLR trigger's code.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Yes, that's the one I had read. I guess I still don't understand why but
that's not as important to me now. So, can the trigger define a parameter?
Create Trigger EnforceInheritanceTriggerOnCar on [Foo].[Car]
For INSERT as External Name
CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
GO
Or do have have to convert my CLRTrigger to a CLRFunction with a parameter
then have the SQL trigger simply call it?
Thanks for your reply!!
"Steven Cheng[MSFT]" wrote:

> Hi Trex,
> Welcome the MSDN newsgroup.
> As for the SQL CLR managed trigger, actually it is designed for binding to
> a certain target table. that's why at runtime the SQLTriggerContext class
> dosn't contain any database/tablename info...
> Also, is the article you mentioned the below one?
> #SQL Server 2005: CLR Integration
> http://blogs.msdn.com/sqlclr/archiv.../21/495438.aspx
> I think the method mentioned there such as using an additional string inpu
t
> parameter to supply table name may be a workaround if you do need to get
> the bound table's name in the CLR trigger's code.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>|||Thanks for your response.
Normal SQL native trigger also dosn't provide parameters. So I think we
still have to create a CLR function (have a parameter) and let our SQL CLR
trigger call that function and pass a fixed name when attatched to a
certain table.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

CLR Triggers, Linq, and Transactions

I'm trying convert my working CLR Trigger to a Linq-based implementation, but I don't seem to be able to get Linq to cooperate with the Trigger's context transaction (a local SubordinateTransaction with IsolationLevel="ReadCommitted").

For brevity's sake I've boiled down my scenario to a bare minimum: A SQL table [Test] with three columns (int Manual, int Automatic, IDENTITY int ID) needs any external updates on its [Manual] column to be applied to its [Automatic] column (with an additional sign change) through a Trigger.

A fairly direct translation of my non-Linq CLR Trigger to Linq yields the following code (the [Trigger_TestLinQs] SQL table has been modified to point to the Trigger's "INSERTED" table, [TestLinQs] points to the actual SQL table):

Code Snippet

[SqlTrigger(Name = "LinQTrigger", Target = "dbo.TestLinQ", Event = "FOR INSERT, UPDATE")]
public static void LinQTrigger()
{
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
if (triggerContext.TriggerAction == TriggerAction.Insert || triggerContext.TriggerAction == TriggerAction.Update) {
//using (TransactionScope scope = new TransactionScope(Transaction.Current)) { /* err 6549 */
using (SqlConnection connection = new SqlConnection("context connection=true")) {
connection.Open();
using (MyDataContext context = new MyDataContext(connection)) {
List<int[]> updates = new List<int[]>();

foreach (Trigger_TestLinQ test in (from t in context.Trigger_TestLinQs select t)) { /* err 6522 */
if (test.Manual != null && triggerContext.IsUpdatedColumn(0))
updates.Add(new int[] { test.ID, -test.Manual.Value });
}

foreach (int[] update in updates) {
dbo.TestLinQ test = (from t in context.TestLinQs where t.ID == update[0] select t).Single<dbo.TestLinQ>();
test.Automatic = update[1];
}

context.SubmitChanges();
}
}
// scope.Complete();
//}
}
}

Running this on SQL Server 2005 / .NET 3.5 beta2 produces an Error 6522 "Cannot enlist in the transaction because a local transaction is in progress on the connection." in the line executing the Linq query because the Linq DataContext apparently insists on opening a transaction.
The recommended pattern for DataContext query operations - running them inside a TransactionScope as indicated by the commented-out sections - unfortunately leads to a different Error 6549 in the TransactionScope constructor complaining about a missing MSDTC. And there shouldn't be a need for a distributed transaction here, should there?
Using different TransactionScope constructors (TransactionScopeOptions.Required, TransactionOptions(){IsolationLevel=ReadCommitted}) did not make any difference, either.

What am I doing wrong here? Is there any way for the Linq DataContext to attach to the CLR Trigger's ambient transaction?
Interesting scenario! I won't ask why you'd want to use LINQ in this way :-)

Anyway, first of all I'm a little bit surprised that LINQ works at all within SQLCLR. I thought it would not work with a Context Connection at all.

It looks like, at the moment, that LINQ does not work very well with transactions when running inside SQLCLR (in fact it looks it doesn't work at all). So at the moment, I don't think there is anything you can do.

Niels
|||Hi nielsb,

thanks for destroying my last hope :-).

You're probably right in that presently LinQ DataContexts don't fully - or maybe even not at all - interoperate with a Context Connection.
I'm wondering why that is the case, however, since it's basically just a case of O/R mapping - which can apparently be coaxed to work with dynamic SQL Tables - and of attaching to a local System.Transactions.Transaction.

Interestingly enough you can make the LinQ DataContext work for the SELECT statement embedded in the trigger by explicitely opening a new Transaction on the SqlConnection. You can't effectively perform any updates, though, because the DataContext's change tracking mechanism doesn't kick in.

As to why I'd want to use LinQ in this context - it's the logical next step once you've been lured away from the glory of T-SQL into the murky depths of SQL Server CLR integration. :-]

CLR Triggers and inserted table

I have created a CLR trigger that validates an address that has been enetere
d
into our address database does not already exist and that it meets country
critera for addressing.
I have a RowVersion column (timestamp), I use this to determin if any
changes have been made to a record in the table on update saving me having t
o
write long SELECT statements.
The problem I have is when I update a row in one instance of Manegement
Studio then try to update in another instance no rows are returned in the
inserted table. What I would like to have happen is a check to see if someon
e
else has changed the row and if so fail the trigger. My SQL trigger works
fine and does everything I need except all the complicated validation and is
why I have choosen to write using the CLR.
At present I have converted the following SQL to C#
CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
INSERT, UPDATE AS
SET NOCOUNT ON;
IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
[inserted].[CountryCode] = [Countries].[CountryCode]
WHERE
NULLIF(LTRIM(RTRIM([Line1])),'') IS NU
LL AND [Line1Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line2])),'') IS NU
LL AND [Line2Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line3])),'') IS NU
LL AND [Line3Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line4])),'') IS NU
LL AND [Line4Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line5])),'') IS NU
LL AND [Line5Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line6])),'') IS NU
LL AND [Line6Required] = 1
OR
NULLIF(LTRIM(RTRIM([Line7])),'') IS NU
LL AND [Line7Required] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NULL AND [PostalCodeRequired] = 1
OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
LIKE [PostalCodeRegExpression] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMinimumCharacters]
<> 0 AND LEN([PostalCode]) <
[PostalCodeMinimumCharacters] OR
NULLIF(LTRIM(RTRIM([PostalCode])),'') IS
NOT
NULL AND [PostalCodeMaximumCharacters]
<> 0 AND LEN([PostalCode]) >
[PostalCodeMaximumCharacters])
RAISERROR('Address does not meet the Countries address criteria.',16,1)
ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
([inserted].[CountryCode] = [Addresses].[CountryCode] OR [inserted].[CountryCode] IS
NULL AND [Addresses].[CountryCode] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') = [Addresses]. [BuildingName] OR NULLIF(LTRIM(RTRIM([i
nserted].[BuildingName])),'') IS
NULL AND [Addresses].[BuildingName] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') = [Addresses]. [BuildingNumber] OR NULLIF(LTRIM(RTRIM([
inserted].[BuildingNumber])),'') IS
NULL AND [Addresses].[BuildingNumber] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses]. [Line1] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine1])),'') IS
NULL AND [Addresses].[Line1] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses]. [Line2] OR NULLIF(LTRIM(RTRIM([insert
ed].[
Line2])),'') IS
NULL AND [Addresses].[Line2] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses]. [Line3] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine3])),'') IS
NULL AND [Addresses].[Line3] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses]. [Line4] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine4])),'') IS
NULL AND [Addresses].[Line4] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses]. [Line5] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine5])),'') IS
NULL AND [Addresses].[Line5] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses]. [Line6] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine6])),'') IS
NULL AND [Addresses].[Line6] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses]. [Line7] OR NULLIF(LTRIM(RTRIM([insert
ed].[L
ine7])),'') IS
NULL AND [Addresses].[Line7] IS NULL) AND
(NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') = [Addresses]. [PostalCode] OR NULLIF(LTRIM(RTRIM([ins
erted].[PostalCode])),'') IS
NULL AND [Addresses].[PostalCode] IS NULL))
RAISERROR('Address already exists.',16,1)
ELSE
IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
BEGIN
INSERT INTO Addresses (
[Addresses].[CountryCode],
[Addresses].[BuildingName],
[Addresses].[BuildingNumber],
[Addresses].[Line1],
[Addresses].[Line2],
[Addresses].[Line3],
[Addresses].[Line4],
[Addresses].[Line5],
[Addresses].[Line6],
[Addresses].[Line7],
[Addresses].[PostalCode]
)
SELECT
[inserted].[CountryCode],
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
FROM [inserted]
END
ELSE -- Update Address
BEGIN
IF EXISTS (
SELECT
NULL
FROM
[inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
[Addresses].[AddressID] AND [inserted].[RowVersion] = [Addresses].[RowVersion]
)
UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses]. [BuildingName] = NULLIF(LTRIM(RTRIM(
[inserted].[BuildingName])),''),
[Addresses]. [BuildingNumber] = NULLIF(LTRIM(RTRIM
([inserted].[BuildingNumber])),''),
[Addresses]. [Line1] = NULLIF(LTRIM(RTRIM([inse
rted].[Line1])),''),
[Addresses]. [Line2] = NULLIF(LTRIM(RTRIM([inse
rted].[Line2])),''),
[Addresses]. [Line3] = NULLIF(LTRIM(RTRIM([inse
rted].[Line3])),''),
[Addresses]. [Line4] = NULLIF(LTRIM(RTRIM([inse
rted].[Line4])),''),
[Addresses]. [Line5] = NULLIF(LTRIM(RTRIM([inse
rted].[Line5])),''),
[Addresses]. [Line6] = NULLIF(LTRIM(RTRIM([inse
rted].[Line6])),''),
[Addresses]. [Line7] = NULLIF(LTRIM(RTRIM([inse
rted].[Line7])),''),
[Addresses]. [PostalCode] = NULLIF(LTRIM(RTRIM([i
nserted].[PostalCode])),'')
FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
ELSE
RAISERROR('Address has since been modified.',16,1)
END
My tables are as follows:
CREATE TABLE [dbo].[Addresses](
[AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[CountryCode] [smallint] NOT NULL CONSTRAINT
[DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
[BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
[Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
[PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
(
[AddressID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
(
[RowVersion] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
CREATE TABLE [dbo].[Countries](
[CountryCode] [smallint] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line1Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine1Required] DEFAULT ((0)),
[Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
DEFAULT ((0)),
[Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
else (1) end,(0))),
[Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line2Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine2Required] DEFAULT ((0)),
[Line2ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line2ForceUppser]
DEFAULT ((0)),
[Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
else (1) end,(0))),
[Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line3Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine3Required] DEFAULT ((0)),
[Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
DEFAULT ((0)),
[Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
else (1) end,(0))),
[Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line4Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine4Required] DEFAULT ((0)),
[Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
DEFAULT ((0)),
[Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
else (1) end,(0))),
[Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line5Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine5Required] DEFAULT ((0)),
[Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
DEFAULT ((0)),
[Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
else (1) end,(0))),
[Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line6Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine6Required] DEFAULT ((0)),
[Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
DEFAULT ((0)),
[Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
else (1) end,(0))),
[Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[Line7Required] [bit] NOT NULL CONSTRAINT
[DF_Countries_AddressLine7Required] DEFAULT ((0)),
[Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
DEFAULT ((0)),
[Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
else (1) end,(0))),
[PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeRequired] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeRequired] DEFAULT ((1)),
[PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
[PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
then (0) else (1) end,(0))),
[PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
[PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
(
[CountryCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [Eddie]
GO
ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
[FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
REFERENCES [dbo].[Countries] ([CountryCode])
My CLR is:
using System;
using System.ComponentModel;
using System.Collections.Generic;
using System.Diagnostics;
using System.Text;
using System.Data;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Xml;
namespace CoullByte.Eddie.SQL.Address.Triggers
{
/// <summary>
/// Address Trigger
/// </summary>
public class Address
{
/// <summary>
/// Prevents Duplicate and Validates the information being stored
/// </summary>
[SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
= "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
public static void DuplicateValidate()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
SqlTriggerContext triggerContext = SqlContext.TriggerContext;
SqlCommand command;
SqlPipe pipe = SqlContext.Pipe;
SqlDataReader reader;
#region Address Declaration
decimal AddressID;
byte[] RowVersion;
short CountryCode;
object BuildingName;
object BuildingNumber;
object Line1;
object Line2;
object Line3;
object Line4;
object Line5;
object Line6;
object Line7;
object PostalCode;
#endregion
#region Country Validation Declaration
bool Line1Required;
bool Line1Visible;
bool Line2Required;
bool Line2Visible;
bool Line3Required;
bool Line3Visible;
bool Line4Required;
bool Line4Visible;
bool Line5Required;
bool Line5Visible;
bool Line6Required;
bool Line6Visible;
bool Line7Required;
bool Line7Visible;
bool PostalCodeRequired;
bool PostalCodeVisible;
object PostalCodeRegExpression;
int PostalCodeMinimumCharacters;
int PostalCodeMaximumCharacters;
#endregion
try
{
switch (triggerContext.TriggerAction)
{
case TriggerAction.Insert:
break;
case TriggerAction.Update:
using (SqlConnection connection = new SqlConnection(@."context
connection=true"))
{
connection.Open();
#region Read Address
command = connection.CreateCommand();
command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
Line7, PostalCode FROM [inserted];";
reader = command.ExecuteReader();
if (!reader.HasRows)
{
throw (new ApplicationException("No Rows"));
}
reader.Read();
if (reader == null)
{
throw (new ApplicationException("Reader"));
}
AddressID = (decimal)reader["AddressID"];
// RowVersion = (byte[])reader["RowVersion"];
CountryCode = (short)reader["CountryCode"];
BuildingName = reader["BuildingName"];
BuildingNumber = reader["BuildingNumber"];
Line1 = reader["Line1"];
Line2 = reader["Line2"];
Line3 = reader["Line3"];
Line4 = reader["Line4"];
Line5 = reader["Line5"];
Line6 = reader["Line6"];
Line7 = reader["Line7"];
PostalCode = reader["PostalCode"];
reader.Close();
#endregion
#region Read Country Validation
command = connection.CreateCommand();
command.CommandText =
@." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
command.CommandType = CommandType.StoredProcedure;
command.Parameters["@.CountryCode"].Value = CountryCode;
reader = command.ExecuteReader();
reader.Read();
Line1Required = (bool)reader["Line1Required"];
Line1Visible = (bool)reader["Line1Visible"];
Line2Required = (bool)reader["Line2Required"];
Line2Visible = (bool)reader["Line2Visible"];
Line3Required = (bool)reader["Line3Required"];
Line3Visible = (bool)reader["Line3Visible"];
Line4Required = (bool)reader["Line4Required"];
Line4Visible = (bool)reader["Line4Visible"];
Line5Required = (bool)reader["Line5Required"];
Line5Visible = (bool)reader["Line5Visible"];
Line6Required = (bool)reader["Line6Required"];
Line6Visible = (bool)reader["Line6Visible"];
Line7Required = (bool)reader["Line7Required"];
Line7Visible = (bool)reader["Line7Visible"];
PostalCodeRequired = (bool)reader["PostalCodeRequired"];
PostalCodeVisible = (bool)reader["PostalCodeVisible"];
PostalCodeRegExpression = reader["PostalCodeRegExpression"];
PostalCodeMinimumCharacters =
(int)reader["PostalCodeMinimumCharacters"];
PostalCodeMaximumCharacters =
(int)reader["PostalCodeMaximumCharacters"];
reader.Close();
#endregion
#region Validate Address
bool addressIsValid =
CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingName
,
BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7, PostalCode,
Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
Line6Required, Line6Visible, Line7Required, Line7Visible, PostalCodeRequired
,
PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
PostalCodeMaximumCharacters);
#endregion
command = connection.CreateCommand();
command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
[Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
[inserted].[RowVersion] = [Addresses].[RowVersion]";
command.CommandType = CommandType.Text;
int addressUnchanged = (int)command.ExecuteScalar();
if (addressUnchanged != 1)
{
throw (new ApplicationException("The address has since been changed
by someone else."));
}
if (!addressIsValid)
{
throw (new ApplicationException("The updated changes made do not
meet the countries address criteria."));
}
command = connection.CreateCommand();
command.CommandText = @."UPDATE [Addresses] SET
[Addresses].[CountryCode] = [inserted].[CountryCode],
[Addresses].[BuildingName] =
NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''), [Addresses].[BuildingNumber]
= NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''), [Addresses].[Line1] =
NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
[inserted].[AddressID] = [Addresses].[AddressID]";
pipe.Send(command.CommandText);
command.ExecuteNonQuery();
}
break;
}
}
catch (System.Data.SqlClient.SqlException sqlExp)
{
throw (sqlExp);
}
catch (System.NullReferenceException nullRef)
{
throw (new ApplicationException(nullRef.StackTrace));
}
catch (System.Exception exp)
{
throw (exp);
}
}
}
}Hi Paul,
What complicated validation? The IS NULL stuff etc... should be done in SQL
too.
Only use CLR if you are going to do something that cannot be better done
using set based methods, such examples would be if you had a post code look
up software to validate the post code exists, you'd probably do that using a
CLR UDF in a CHECK constraint.
Ditch the C# and stick with the SQL trigger - seriously!
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Dr. Paul Caesar - CoullByte (UK) Limited"
< DrPaulCaesarCoullByteUKLimited@.discussio
ns.microsoft.com> wrote in message
news:D04F7671-BB3B-42BB-A3C2-0ADC8810B708@.microsoft.com...
>I have created a CLR trigger that validates an address that has been
>enetered
> into our address database does not already exist and that it meets country
> critera for addressing.
> I have a RowVersion column (timestamp), I use this to determin if any
> changes have been made to a record in the table on update saving me having
> to
> write long SELECT statements.
> The problem I have is when I update a row in one instance of Manegement
> Studio then try to update in another instance no rows are returned in the
> inserted table. What I would like to have happen is a check to see if
> someone
> else has changed the row and if so fail the trigger. My SQL trigger works
> fine and does everything I need except all the complicated validation and
> is
> why I have choosen to write using the CLR.
> At present I have converted the following SQL to C#
> CREATE TRIGGER [AddressDuplicateTrigger] ON [dbo].[Addresses] INSTEAD OF
> INSERT, UPDATE AS
> SET NOCOUNT ON;
> IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Countries] ON
> [inserted].[CountryCode] = [Countries].[CountryCode]
> WHERE
> NULLIF(LTRIM(RTRIM([Line1])),'') IS NULL AND [Line1Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line2])),'') IS NULL AND [Line2Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line3])),'') IS NULL AND [Line3Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line4])),'') IS NULL AND [Line4Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line5])),'') IS NULL AND [Line5Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line6])),'') IS NULL AND [Line6Required] = 1 OR
> NULLIF(LTRIM(RTRIM([Line7])),'') IS NULL AND [Line7Required] = 1 OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NULL AND [PostalCodeRequired] = 1
> OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeRegExpression] IS NOT NULL AND [PostalCode] NOT
> LIKE [PostalCodeRegExpression] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMinimumCharacters] <> 0 AND LEN([PostalCode]) <
> [PostalCodeMinimumCharacters] OR
> NULLIF(LTRIM(RTRIM([PostalCode])),'') IS NOT
> NULL AND [PostalCodeMaximumCharacters] <> 0 AND LEN([PostalCode]) >
> [PostalCodeMaximumCharacters])
> RAISERROR('Address does not meet the Countries address criteria.',16,1)
> ELSE IF EXISTS(SELECT NULL FROM [inserted] INNER JOIN [Addresses] ON
> ([inserted].[CountryCode] = [Addresses].[CountryCode] OR
> [inserted].[CountryCode] IS
> NULL AND [Addresses].[CountryCode] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') =
> [Addresses].[BuildingName] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),'') IS
> NULL AND [Addresses].[BuildingName] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') =
> [Addresses].[BuildingNumber] OR
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),'') IS
> NULL AND [Addresses].[BuildingNumber] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') = [Addresses].[Line1] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),'') IS
> NULL AND [Addresses].[Line1] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line2] )),'') = [Addresses].[Line2] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),'') IS
> NULL AND [Addresses].[Line2] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') = [Addresses].[Line3] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),'') IS
> NULL AND [Addresses].[Line3] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') = [Addresses].[Line4] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),'') IS
> NULL AND [Addresses].[Line4] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') = [Addresses].[Line5] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),'') IS
> NULL AND [Addresses].[Line5] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') = [Addresses].[Line6] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),'') IS
> NULL AND [Addresses].[Line6] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') = [Addresses].[Line7] OR
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),'') IS
> NULL AND [Addresses].[Line7] IS NULL) AND
> (NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') =
> [Addresses].[PostalCode] OR
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') IS
> NULL AND [Addresses].[PostalCode] IS NULL))
> RAISERROR('Address already exists.',16,1)
> ELSE
> IF (NOT EXISTS (SELECT NULL FROM [deleted])) -- Insert Address
> BEGIN
> INSERT INTO Addresses (
> [Addresses].[CountryCode],
> [Addresses].[BuildingName],
> [Addresses].[BuildingNumber],
> [Addresses].[Line1],
> [Addresses].[Line2],
> [Addresses].[Line3],
> [Addresses].[Line4],
> [Addresses].[Line5],
> [Addresses].[Line6],
> [Addresses].[Line7],
> [Addresses].[PostalCode]
> )
> SELECT
> [inserted].[CountryCode],
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted]
> END
> ELSE -- Update Address
> BEGIN
> IF EXISTS (
> SELECT
> NULL
> FROM
> [inserted] INNER JOIN [Addresses] ON [inserted].[AddressID] =
> [Addresses].[AddressID] AND [inserted].[RowVersion] =
> [Addresses].[RowVersion]
> )
> UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] = NULLIF(LTRIM(RTRIM([inserted].[Line1])),''),
> [Addresses].[Line2] = NULLIF(LTRIM(RTRIM([inserted].[Line2])),''),
> [Addresses].[Line3] = NULLIF(LTRIM(RTRIM([inserted].[Line3])),''),
> [Addresses].[Line4] = NULLIF(LTRIM(RTRIM([inserted].[Line4])),''),
> [Addresses].[Line5] = NULLIF(LTRIM(RTRIM([inserted].[Line5])),''),
> [Addresses].[Line6] = NULLIF(LTRIM(RTRIM([inserted].[Line6])),''),
> [Addresses].[Line7] = NULLIF(LTRIM(RTRIM([inserted].[Line7])),''),
> [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'')
> FROM [inserted] WHERE [inserted].[AddressID] = [Addresses].[AddressID]
> ELSE
> RAISERROR('Address has since been modified.',16,1)
> END
> My tables are as follows:
> CREATE TABLE [dbo].[Addresses](
> [AddressID] [numeric](8, 0) IDENTITY(10000001,1) NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [CountryCode] [smallint] NOT NULL CONSTRAINT
> [DF_Entities_Addresses_CountryCode] DEFAULT ((44)),
> [BuildingName] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [BuildingNumber] [nvarchar](15) COLLATE Latin1_General_CI_AS NULL,
> [Line1] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line2] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line3] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line4] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line5] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line6] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [Line7] [nvarchar](80) COLLATE Latin1_General_CI_AS NULL,
> [PostalCode] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> CONSTRAINT [PK_Entities_Addresses] PRIMARY KEY CLUSTERED
> (
> [AddressID] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY],
> CONSTRAINT [IX_Addresses] UNIQUE NONCLUSTERED
> (
> [RowVersion] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> CREATE TABLE [dbo].[Countries](
> [CountryCode] [smallint] NOT NULL,
> [RowVersion] [timestamp] NOT NULL,
> [Country] [nvarchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
> [Line1Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line1ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line1Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine1Required] DEFAULT ((0)),
> [Line1ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line1ForceUpper]
> DEFAULT ((0)),
> [Line1Visible] AS (CONVERT([bit],case when [Line1Label] IS NULL then (0)
> else (1) end,(0))),
> [Line2Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line2ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line2Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine2Required] DEFAULT ((0)),
> [Line2ForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_Line2ForceUppser]
> DEFAULT ((0)),
> [Line2Visible] AS (CONVERT([bit],case when [Line2Label] IS NULL then (0)
> else (1) end,(0))),
> [Line3Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line3ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line3Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine3Required] DEFAULT ((0)),
> [Line3ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line3ForceUpper]
> DEFAULT ((0)),
> [Line3Visible] AS (CONVERT([bit],case when [Line3Label] IS NULL then (0)
> else (1) end,(0))),
> [Line4Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line4ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line4Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine4Required] DEFAULT ((0)),
> [Line4ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line4ForceUpper]
> DEFAULT ((0)),
> [Line4Visible] AS (CONVERT([bit],case when [Line4Label] IS NULL then (0)
> else (1) end,(0))),
> [Line5Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line5ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line5Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine5Required] DEFAULT ((0)),
> [Line5ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line5ForceUpper]
> DEFAULT ((0)),
> [Line5Visible] AS (CONVERT([bit],case when [Line5Label] IS NULL then (0)
> else (1) end,(0))),
> [Line6Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line6ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line6Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine6Required] DEFAULT ((0)),
> [Line6ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line6ForceUpper]
> DEFAULT ((0)),
> [Line6Visible] AS (CONVERT([bit],case when [Line6Label] IS NULL then (0)
> else (1) end,(0))),
> [Line7Label] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [Line7ToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [Line7Required] [bit] NOT NULL CONSTRAINT
> [DF_Countries_AddressLine7Required] DEFAULT ((0)),
> [Line7ForceUpper] [bit] NOT NULL CONSTRAINT [DF_Countries_Line7ForceUpper]
> DEFAULT ((0)),
> [Line7Visible] AS (CONVERT([bit],case when [Line7Label] IS NULL then (0)
> else (1) end,(0))),
> [PostalCodeLabel] [nvarchar](20) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeToolTip] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
> [PostalCodeRequired] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeRequired] DEFAULT ((1)),
> [PostalCodeForceUpper] [bit] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeForceUpper] DEFAULT ((1)),
> [PostalCodeVisible] AS (CONVERT([bit],case when [PostalCodeLabel] IS NULL
> then (0) else (1) end,(0))),
> [PostalCodeRegExpression] [nvarchar](255) COLLATE Latin1_General_CI_AS
> NULL,
> [PostalCodeMinimumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMinimumCharacter
s] DEFAULT ((0)),
> [PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
> [DF_Countries_PostalCodeMaximumCharacter
s] DEFAULT ((0)),
> CONSTRAINT [PK_Countries] PRIMARY KEY CLUSTERED
> (
> [CountryCode] ASC
> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> ) ON [PRIMARY]
>
> GO
> USE [Eddie]
> GO
> ALTER TABLE [dbo].[Addresses] WITH CHECK ADD CONSTRAINT
> [FK_Entities_Addresses_Countries] FOREIGN KEY([CountryCode])
> REFERENCES [dbo].[Countries] ([CountryCode])
> My CLR is:
> using System;
> using System.ComponentModel;
> using System.Collections.Generic;
> using System.Diagnostics;
> using System.Text;
> using System.Data;
> using System.Data.Sql;
> using Microsoft.SqlServer.Server;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.Xml;
> namespace CoullByte.Eddie.SQL.Address.Triggers
> {
> /// <summary>
> /// Address Trigger
> /// </summary>
> public class Address
> {
> /// <summary>
> /// Prevents Duplicate and Validates the information being stored
> /// </summary>
> [SqlTrigger(Name = @." Eddie_v6_Address_Duplicate_Validate_Trig
ger", Target
> = "[dbo].[Addresses]", Event = "INSTEAD OF INSERT, UPDATE")]
> public static void DuplicateValidate()
> {
> if (!SqlContext.IsAvailable)
> {
> throw (new NotSupportedException());
> }
>
> SqlTriggerContext triggerContext = SqlContext.TriggerContext;
> SqlCommand command;
> SqlPipe pipe = SqlContext.Pipe;
> SqlDataReader reader;
> #region Address Declaration
> decimal AddressID;
> byte[] RowVersion;
> short CountryCode;
> object BuildingName;
> object BuildingNumber;
> object Line1;
> object Line2;
> object Line3;
> object Line4;
> object Line5;
> object Line6;
> object Line7;
> object PostalCode;
> #endregion
> #region Country Validation Declaration
> bool Line1Required;
> bool Line1Visible;
> bool Line2Required;
> bool Line2Visible;
> bool Line3Required;
> bool Line3Visible;
> bool Line4Required;
> bool Line4Visible;
> bool Line5Required;
> bool Line5Visible;
> bool Line6Required;
> bool Line6Visible;
> bool Line7Required;
> bool Line7Visible;
> bool PostalCodeRequired;
> bool PostalCodeVisible;
> object PostalCodeRegExpression;
> int PostalCodeMinimumCharacters;
> int PostalCodeMaximumCharacters;
> #endregion
> try
> {
> switch (triggerContext.TriggerAction)
> {
> case TriggerAction.Insert:
> break;
> case TriggerAction.Update:
> using (SqlConnection connection = new SqlConnection(@."context
> connection=true"))
> {
> connection.Open();
> #region Read Address
> command = connection.CreateCommand();
> command.CommandText = @."SELECT AddressID, RowVersion, CountryCode,
> BuildingName, BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6,
> Line7, PostalCode FROM [inserted];";
> reader = command.ExecuteReader();
> if (!reader.HasRows)
> {
> throw (new ApplicationException("No Rows"));
> }
> reader.Read();
> if (reader == null)
> {
> throw (new ApplicationException("Reader"));
> }
> AddressID = (decimal)reader["AddressID"];
> // RowVersion = (byte[])reader["RowVersion"];
> CountryCode = (short)reader["CountryCode"];
> BuildingName = reader["BuildingName"];
> BuildingNumber = reader["BuildingNumber"];
> Line1 = reader["Line1"];
> Line2 = reader["Line2"];
> Line3 = reader["Line3"];
> Line4 = reader["Line4"];
> Line5 = reader["Line5"];
> Line6 = reader["Line6"];
> Line7 = reader["Line7"];
> PostalCode = reader["PostalCode"];
> reader.Close();
> #endregion
> #region Read Country Validation
> command = connection.CreateCommand();
> command.CommandText =
> @." Eddie_v6_Country_Select_Validation_By_Co
untryCode";
> command.Parameters.Add("@.CountryCode", SqlDbType.SmallInt, 0);
> command.CommandType = CommandType.StoredProcedure;
> command.Parameters["@.CountryCode"].Value = CountryCode;
> reader = command.ExecuteReader();
> reader.Read();
> Line1Required = (bool)reader["Line1Required"];
> Line1Visible = (bool)reader["Line1Visible"];
> Line2Required = (bool)reader["Line2Required"];
> Line2Visible = (bool)reader["Line2Visible"];
> Line3Required = (bool)reader["Line3Required"];
> Line3Visible = (bool)reader["Line3Visible"];
> Line4Required = (bool)reader["Line4Required"];
> Line4Visible = (bool)reader["Line4Visible"];
> Line5Required = (bool)reader["Line5Required"];
> Line5Visible = (bool)reader["Line5Visible"];
> Line6Required = (bool)reader["Line6Required"];
> Line6Visible = (bool)reader["Line6Visible"];
> Line7Required = (bool)reader["Line7Required"];
> Line7Visible = (bool)reader["Line7Visible"];
> PostalCodeRequired = (bool)reader["PostalCodeRequired"];
> PostalCodeVisible = (bool)reader["PostalCodeVisible"];
> PostalCodeRegExpression = reader["PostalCodeRegExpression"];
> PostalCodeMinimumCharacters =
> (int)reader["PostalCodeMinimumCharacters"];
> PostalCodeMaximumCharacters =
> (int)reader["PostalCodeMaximumCharacters"];
> reader.Close();
> #endregion
> #region Validate Address
> bool addressIsValid =
> CoullByte.Eddie.SQL.Address.Functions.Validation.AddressIsValid(BuildingNa
me,
> BuildingNumber, Line1, Line2, Line3, Line4, Line5, Line6, Line7,
> PostalCode,
> Line1Required, Line1Visible, Line2Required, Line2Visible, Line3Required,
> Line3Visible, Line4Required, Line4Visible, Line5Required, Line5Visible,
> Line6Required, Line6Visible, Line7Required, Line7Visible,
> PostalCodeRequired,
> PostalCodeVisible, PostalCodeRegExpression, PostalCodeMinimumCharacters,
> PostalCodeMaximumCharacters);
> #endregion
> command = connection.CreateCommand();
> command.CommandText = @."SELECT COUNT(*) FROM [inserted] INNER JOIN
> [Addresses] ON [inserted].[AddressID] = [Addresses].[AddressID] AND
> [inserted].[RowVersion] = [Addresses].[RowVersion]";
> command.CommandType = CommandType.Text;
> int addressUnchanged = (int)command.ExecuteScalar();
> if (addressUnchanged != 1)
> {
> throw (new ApplicationException("The address has since been changed
> by someone else."));
> }
> if (!addressIsValid)
> {
> throw (new ApplicationException("The updated changes made do not
> meet the countries address criteria."));
> }
> command = connection.CreateCommand();
> command.CommandText = @."UPDATE [Addresses] SET
> [Addresses].[CountryCode] = [inserted].[CountryCode],
> [Addresses].[BuildingName] =
> NULLIF(LTRIM(RTRIM([inserted].[BuildingName])),''),
> [Addresses].[BuildingNumber]
> = NULLIF(LTRIM(RTRIM([inserted].[BuildingNumber])),''),
> [Addresses].[Line1] =
> NULLIF(LTRIM(RTRIM([inserted].[Line1])),''), [Addresses].[Line2] =
> NULLIF(LTRIM(RTRIM([inserted].[Line2])),''), [Addresses].[Line3] =
> NULLIF(LTRIM(RTRIM([inserted].[Line3])),''), [Addresses].[Line4] =
> NULLIF(LTRIM(RTRIM([inserted].[Line4])),''), [Addresses].[Line5] =
> NULLIF(LTRIM(RTRIM([inserted].[Line5])),''), [Addresses].[Line6] =
> NULLIF(LTRIM(RTRIM([inserted].[Line6])),''), [Addresses].[Line7] =
> NULLIF(LTRIM(RTRIM([inserted].[Line7])),''), [Addresses].[PostalCode] =
> NULLIF(LTRIM(RTRIM([inserted].[PostalCode])),'') FROM [inserted] WHERE
> [inserted].[AddressID] = [Addresses].[AddressID]";
> pipe.Send(command.CommandText);
> command.ExecuteNonQuery();
> }
> break;
> }
> }
> catch (System.Data.SqlClient.SqlException sqlExp)
> {
> throw (sqlExp);
> }
> catch (System.NullReferenceException nullRef)
> {
> throw (new ApplicationException(nullRef.StackTrace));
> }
> catch (System.Exception exp)
> {
> throw (exp);
> }
> }
> }
> }
>|||In my orginal post I said "...and that it meets country
critera for addressing...". The CLR is the only way I can validate an
address is enetered that meets the countries addressing criteria. SQL
triggers do not allow me to do this and is why I chose to use c#. All
countries are stored in the Countries table and each country have diffrent
ways of handling addresses such as that between UK and US. US use Zip Code
and UK use Post Code for example and US Zip codes are not the same format as
UK Post Codes. In the address table I have columns such as Building Name,
Building Number, Line 1, Line 2 - Line 7 then Postal Code. When using our ow
n
software changing country code changes labels and validation requirements on
the Windows Form or on the Web Forms. The c# trigger validates based on the
country and forces NULL into the columns where their is empty strings etc.
Hope this help to explain what I am trying to acheive.|||Based on the code snippet you post, you might have some design issues as
well. Can you post the table structures, a few sample data along with the
address validation rules you have?
In general, Tony's last statement in his response would be a suitable
advice.
Anith|||My Address Validation class is as follows, other stuff already posted and
table scripts also posted:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Server;
namespace CoullByte.Eddie.SQL.Address.Functions
{
/// <summary>
/// Address Validation Class
/// </summary>
public class Validation
{
#region SQL Context
/// <summary>
/// Validate if running under SQL
/// </summary>
private static void ValidateSQLContext()
{
if (!SqlContext.IsAvailable)
{
throw (new NotSupportedException());
}
}
#endregion
#region Address Is Valid
/// <summary>
/// Address Data Validation
/// </summary>
/// <param name="BuildingName">Building Name</param>
/// <param name="BuildingNumber">Building Number</param>
/// <param name="Line1">Line 1</param>
/// <param name="Line2">Line 2</param>
/// <param name="Line3">Line 3</param>
/// <param name="Line4">Line 4</param>
/// <param name="Line5">Line 5</param>
/// <param name="Line6">Line 6</param>
/// <param name="Line7">Line 7</param>
/// <param name="PostalCode">Postal Code</param>
/// <param name="Line1Required">Line 1 Required?</param>
/// <param name="Line1Visible">Line 1 Visible to User?</param>
/// <param name="Line2Required">Line 2 Required?</param>
/// <param name="Line2Visible">Line 2 Visible to User?</param>
/// <param name="Line3Required">Line 3 Required?</param>
/// <param name="Line3Visible">Line 3 Visible to User?</param>
/// <param name="Line4Required">Line 4 Required?</param>
/// <param name="Line4Visible">Line 4 Visible to User?</param>
/// <param name="Line5Required">Line 5 Required?</param>
/// <param name="Line5Visible">Line 5 Visible to User?</param>
/// <param name="Line6Required">Line 6 Required?</param>
/// <param name="Line6Visible">Line 6 Visible to User?</param>
/// <param name="Line7Required">Line 7 Required?</param>
/// <param name="Line7Visible">Line 7 Visible to User?</param>
/// <param name="PostalCodeRequired">Postal Code Required?</param>
/// <param name="PostalCodeVisible">Postal Code Visible?</param>
/// <param name="PostalCodeRegExpression">Postal Code Regular
Expression</param>
/// <param name="PostalCodeMinimumCharacters">Postal Code Minimum
Characters</param>
/// <param name="PostalCodeMaximumCharacters">Postal Code Maximum
Characters</param>
/// <returns>Validation Result</returns>
public static bool AddressIsValid(object BuildingName, object
BuildingNumber, object Line1, object Line2, object Line3, object Line4,
object Line5, object Line6, object Line7, object PostalCode, bool
Line1Required, bool Line1Visible, bool Line2Required, bool Line2Visible, boo
l
Line3Required, bool Line3Visible, bool Line4Required, bool Line4Visible, boo
l
Line5Required, bool Line5Visible, bool Line6Required, bool Line6Visible, boo
l
Line7Required, bool Line7Visible, bool PostalCodeRequired, bool
PostalCodeVisible, object PostalCodeRegExpression, int
PostalCodeMinimumCharacters, int PostalCodeMaximumCharacters)
{
return Line1IsValid(Line1Required, Line1Visible, Line1) &&
Line2IsValid(Line2Required, Line2Visible, Line2) &&
Line3IsValid(Line3Required, Line3Visible, Line3) &&
Line4IsValid(Line4Required, Line4Visible, Line4) &&
Line5IsValid(Line5Required, Line5Visible, Line5) &&
Line6IsValid(Line6Required, Line6Visible, Line6) &&
Line7IsValid(Line7Required, Line7Visible, Line7) &&
PostalCodeIsValid(PostalCodeRequired, PostalCodeVisible,
PostalCodeMinimumCharacters, PostalCodeMaximumCharacters,
PostalCodeRegExpression, PostalCode);
}
#endregion
#region Line 1 Data Validation
/// <summary>
/// Line 1 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line1IsValid (bool Line1Required, bool Line1Visible,
object Line1Data)
{
ValidateSQLContext();
if (!Line1Visible)
{
return true;
}
else
{
return ((!Line1Required) || (Line1Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line1Data)));
}
}
#endregion
#region Line 2 Data Validation
/// <summary>
/// Line 2 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line2IsValid(bool Line2Required, bool Line2Visible,
object Line2Data)
{
ValidateSQLContext();
if (!Line2Visible)
{
return true;
}
else
{
return ((!Line2Required) || (Line2Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line2Data)));
}
}
#endregion
#region Line 3 Data Validation
/// <summary>
/// Line 3 Data Validation
/// </summary>
/// <param name="Line3Required">Data Required?</param>
/// <param name="Line3Visible">Visible to User?</param>
/// <param name="Line3Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line3IsValid(bool Line3Required, bool Line3Visible,
object Line3Data)
{
ValidateSQLContext();
if (!Line3Visible)
{
return true;
}
else
{
return ((!Line3Required) || (Line3Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line3Data)));
}
}
#endregion
#region Line 4 Data Validation
/// <summary>
/// Line 4 Data Validation
/// </summary>
/// <param name="Line4Required">Data Required?</param>
/// <param name="Line4Visible">Visible to User?</param>
/// <param name="Line4Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line4IsValid(bool Line4Required, bool Line4Visible,
object Line4Data)
{
ValidateSQLContext();
if (!Line4Visible)
{
return true;
}
else
{
return ((!Line4Required) || (Line4Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line4Data)));
}
}
#endregion
#region Line 5 Data Validation
/// <summary>
/// Line 5 Data Validation
/// </summary>
/// <param name="Line1Required">Data Required?</param>
/// <param name="Line1Visible">Visible to User?</param>
/// <param name="Line1Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line5IsValid(bool Line5Required, bool Line5Visible,
object Line5Data)
{
ValidateSQLContext();
if (!Line5Visible)
{
return true;
}
else
{
return ((!Line5Required) || (Line5Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line5Data)));
}
}
#endregion
#region Line 6 Data Validation
/// <summary>
/// Line 6 Data Validation
/// </summary>
/// <param name="Line6Required">Data Required?</param>
/// <param name="Line6Visible">Visible to User?</param>
/// <param name="Line6Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line6IsValid(bool Line6Required, bool Line6Visible,
object Line6Data)
{
if (!Line6Visible)
{
return true;
}
else
{
return ((!Line6Required) || (Line6Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line6Data)));
}
}
#endregion
#region Line 7 Data Validation
/// <summary>
/// Line 7 Data Validation
/// </summary>
/// <param name="Line7Required">Data Required?</param>
/// <param name="Line7Visible">Visible to User?</param>
/// <param name="Line7Data">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool Line7IsValid(bool Line7Required, bool Line7Visible,
object Line7Data)
{
if (!Line7Visible)
{
return true;
}
else
{
return ((!Line7Required) || (Line7Required &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(Line7Data)));
}
}
#endregion
#region Postal Code Validation
/// <summary>
/// Postal Code Validation
/// </summary>
/// <param name="PostalCodeRequired">Data Required</param>
/// <param name="PostalCodeVisible">Visible to User?</param>
/// <param name="PostalCodeMinimumCharacters">Minimum Characters
Required?</param>
/// <param name="PostalCodeMaximumCharacters">Maximum Characters
Required?</param>
/// <param name="PostalCodeRegExpression">Regular Expression for
Validating Data</param>
/// <param name="PostalCodeData">Data to Validate</param>
/// <returns>Validation Result</returns>
public static bool PostalCodeIsValid(bool PostalCodeRequired, bool
PostalCodeVisible, int PostalCodeMinimumCharacters, int
PostalCodeMaximumCharacters, object PostalCodeRegExpression, object
PostalCodeData)
{
string postalCodeData = string.Empty;
string postalCodeRegExpression = string.Empty;
if (PostalCodeRegExpression != null && PostalCodeRegExpression !=
DBNull.Value && PostalCodeRegExpression is string)
{
postalCodeRegExpression = Convert.ToString(PostalCodeRegExpression);
}
if (PostalCodeData != null && PostalCodeData != DBNull.Value &&
PostalCodeData is string)
{
postalCodeData = Convert.ToString(PostalCodeData);
}
bool lengthIsValid =
(
(
PostalCodeMinimumCharacters == 0
) ||
(
PostalCodeMinimumCharacters != 0 &&
postalCodeData.Length >= PostalCodeMinimumCharacters
) &&
(
(
PostalCodeMaximumCharacters == 0
) ||
(
PostalCodeMaximumCharacters != 0 &&
postalCodeData.Length <= PostalCodeMaximumCharacters
)
)
);
bool regExpIsValid =
(
(
postalCodeRegExpression == string.Empty
) ||
(
CoullByte.Eddie.SQL.Functions.Data.IsMeaningless(PostalCodeRegExpression)
) ||
(
postalCodeRegExpression != string.Empty &&
CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(postalCodeRegExpression) &&
System.Text.RegularExpressions.Regex.IsMatch(postalCodeData,
postalCodeRegExpression)
)
);
if (PostalCodeRequired)
{
return (CoullByte.Eddie.SQL.Functions.Data.IsMeaningful(PostalCodeData)
&& lengthIsValid && regExpIsValid);
}
else
{
return (lengthIsValid && regExpIsValid);
}
}
#endregion
}
}
a sample row for the Countries table is as follows:
INSERT INTO [Eddie].[dbo].[Countries]
([CountryCode]
,[Country]
,[Line1Label]
,[Line1ToolTip]
,[Line1Required]
,[Line1ForceUpper]
,[Line2Label]
,[Line2ToolTip]
,[Line2Required]
,[Line2ForceUpper]
,[Line3Label]
,[Line3ToolTip]
,[Line3Required]
,[Line3ForceUpper]
,[Line4Label]
,[Line4ToolTip]
,[Line4Required]
,[Line4ForceUpper]
,[Line5Label]
,[Line5ToolTip]
,[Line5Required]
,[Line5ForceUpper]
,[Line6Label]
,[Line6ToolTip]
,[Line6Required]
,[Line6ForceUpper]
,[Line7Label]
,[Line7ToolTip]
,[Line7Required]
,[Line7ForceUpper]
,[PostalCodeLabel]
,[PostalCodeToolTip]
,[PostalCodeRequired]
,[PostalCodeForceUpper]
,[PostalCodeRegExpression]
,[PostalCodeMinimumCharacters]
,[PostalCodeMaximumCharacters])
VALUES
(44
,'United Kingdom'
,'Thoroughfare'
,NULL
,0
,0
,'Locality'
,NULL
,0
,0
,'Post Town'
,NULL
,1
,1
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,NULL
,NULL
,0
,0
,'Post Code'
,NULL
,1
,1
,'(GIR0AA)|((([A-Z-[QVX]][0-9][0-9]?)|(([A-Z-[QVX]][A-Z-[IJZ]][0-9][0-9]?)|(([A-Z-[QVX]][0-9][A-HJKSTUW])|([A-Z-[QVX]][A
-Z-[IJZ]][0-9][ABEHMNPRVWXY]))))
[0-9][A-Z-[CIKMOV]]{2})'
,3
,8)
A sample address for the Addresses table is:
INSERT INTO [Eddie].[dbo].[Addresses]
([CountryCode]
,[BuildingName]
,[BuildingNumber]
,[Line1]
,[Line2]
,[Line3]
,[Line4]
,[Line5]
,[Line6]
,[Line7]
,[PostalCode])
VALUES
(44
,NULL
,NULL
,'Queens Walk'
,NULL
,'READING'
,NULL
,NULL
,NULL
,NULL
,'RG1 7PT')

CLR Trigger to write file.......

Hello, theres,

I have a request to write to a file whenever new record added to the table.

When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ?

Regards,

Agi

I assume you are using this to generate an Audit log or something similar. Here is the route I took.

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

|||

Jonathan,

Thanks for your reply, I just want to write everything to the flat file whenever new record inserted to my table. I write a trigger using c#. I just wonder if more than 2 threads (sessions) insert new record at the same time, what will be write to the flat file ?

Regards,

Agi

|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

CLR trigger on another schema

Hi,

I'm trying to write a clr trigger. I have a table under a user defined schema, say Myschema.Table1. If I write something like this

[SqlTrigger(Name = "Trigger1", Target = "Myschema.Table1", Event = "FOR INSERT")]
I got this error when deploying my assembly on SQl server:
Cannot find the object "Myschema.Table1" because it does not exist or you do not have permissions

If I move Table1 in the dbo schema the assembly is deployed succesfully
Can someone help me

Thank you

This is a known bug. You will need to manually deploy the trigger to the non-dbo schema.

CLR Trigger in Problem in Sql Server 2005 april ctp

Hi all,
I've got a problem using various CLR Trigger samples: Most samples include
expressions like the snippet below, using the System.Data.SqlServer namespac
e
to access the SqlContext:
...
using System.Data.SqlServer;
...
SqlTriggerContext stc = SqlContext.GetTriggerContext;
SqlCommand scomm = SqlContext.GetCommand();
scomm.CommandText = "SELECT * from " + "INSERTED";
SqlDataRecord sdr = scomm.ExecuteRow();
String result1 = (string)sdr[0];
...
My problem: I don't have this namespace! Can't find it in my assemblies,
it's not even included in System.Data. Is there a new way for accessing the
exact updated/inserted/deleted data which caused the trigger to execute? I'm
using the april releases of Visual Studio 2005 Beta 2 and SQL Server 2005 CT
P.
thanks in advanceThat should go for the beta newsgroup.
If you make up a new projects within Visual Studio and use the Project SQL
Server Procedure, youll find those namespace to import. if you are running
on the actual Server with Visual Studio they are installed. If you are
running on another machine you probably have to install the native client to
get this namepsaces as well as the extensions to Visual Studio 2005 to use
it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"al" <al@.discussions.microsoft.com> schrieb im Newsbeitrag
news:2AC8B8A5-4275-4D77-801E-5D6080002287@.microsoft.com...
> Hi all,
> I've got a problem using various CLR Trigger samples: Most samples include
> expressions like the snippet below, using the System.Data.SqlServer
> namespace
> to access the SqlContext:
> ...
> using System.Data.SqlServer;
> ...
> SqlTriggerContext stc = SqlContext.GetTriggerContext;
> SqlCommand scomm = SqlContext.GetCommand();
> scomm.CommandText = "SELECT * from " + "INSERTED";
> SqlDataRecord sdr = scomm.ExecuteRow();
> String result1 = (string)sdr[0];
> ...
> My problem: I don't have this namespace! Can't find it in my assemblies,
> it's not even included in System.Data. Is there a new way for accessing
> the
> exact updated/inserted/deleted data which caused the trigger to execute?
> I'm
> using the april releases of Visual Studio 2005 Beta 2 and SQL Server 2005
> CTP.
> thanks in advance|||hi jens,
that helped to solve the problem. looking at the references i found that
this namespace is held in the sqlaccess.dll, located in the sql server
folders.
a minor problem now is a compiler warning, stating that this namespace will
soon be obsolete and the classes be relocated to the system.data.sqlclient
namespace =) currently the move seems incomplete here, so my app will be as
beta as the server ;)
greetz
al
"Jens Sü?meyer" wrote:

> That should go for the beta newsgroup.
> If you make up a new projects within Visual Studio and use the Project SQL
> Server Procedure, you′ll find those namespace to import. if you are runni
ng
> on the actual Server with Visual Studio they are installed. If you are
> running on another machine you probably have to install the native client
to
> get this namepsaces as well as the extensions to Visual Studio 2005 to use
> it.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --|||Nice to hear that it helped.

>so my app will be as
> beta as the server
We share your experience wiht that;-)
But i think thats quite nice from the sql server team because you can plan
it to move this over. In previous version there hasnt been such a compiler
warning. :-)
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"al" <al@.discussions.microsoft.com> schrieb im Newsbeitrag
news:42A09372-8CE6-44FA-991D-4896A79C680A@.microsoft.com...
> hi jens,
> that helped to solve the problem. looking at the references i found that
> this namespace is held in the sqlaccess.dll, located in the sql server
> folders.
> a minor problem now is a compiler warning, stating that this namespace
> will
> soon be obsolete and the classes be relocated to the system.data.sqlclient
> namespace =) currently the move seems incomplete here, so my app will be
> as
> beta as the server ;)
> greetz
> al
> "Jens Smeyer" wrote:
>

CLR trigger in another schema

Hi,

I'm trying to write a clr trigger. I have created Myschema schema under schemas folder and Table1 under this schema (i.e.: Myschema.Table1).
I created a clr trigger and tagged it with the Sqltrigger attribute as shown here:

[SqlTrigger(Name = "Trigger1", Target = "Myschema.Table1", Event = "FOR INSERT")]

When I try to deploy my assembly from VS 2005 I got the following error:
Cannot find the object "Myschema.Table1" because it does not exist or you do not have permissions

If I move Table1 in the dbo schema the assembly is deployed succesfully

If I add the assembly from the Assemblies folder in sql2005 db, it deploys successfully. then I can define a trigger using this code

CREATE TRIGGER [SampleTrigger] ON [Myschema].[Table1] AFTER INSERT AS

EXTERNAL NAME [CM.Library.Blocks.SecurityEngine.SQL.Functions].[Triggers].[SampleTrigger]

In this manner, the trigger fires successfully but I'm not able to debug it from VS 2005

Can someone help me? Thanks

Thank you

Ugh... that's sad. :( Maybe Niels Berglund's MSBUILD task can help with this: http://www.sqljunkies.com/WebLog/nielsb/archive/2005/05/03/13379.aspx -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Haplo_69@.discussions.microsoft.com> wrote in message news:95f705e6-a736-44ba-8f20-41cc0d5843cd@.discussions.microsoft.com...Hi,I'm trying to write a clr trigger. I have created Myschema schema under schemas folder and Table1 under this schema (i.e.: Myschema.Table1). I created a clr trigger and tagged it with the Sqltrigger attribute as shown here: [SqlTrigger(Name = "Trigger1", Target = "Myschema.Table1", Event = "FOR INSERT")] When I try to deploy my assembly from VS 2005 I got the following error:Cannot find the object "Myschema.Table1" because it does not exist or you do not have permissionsIf I move Table1 in the dbo schema the assembly is deployed succesfullyIf I add the assembly from the Assemblies folder in sql2005 db, it deploys successfully. then I can define a trigger using this codeCREATE TRIGGER [SampleTrigger] ON [Myschema].[Table1] AFTER INSERT AS EXTERNAL NAME [CM.Library.Blocks.SecurityEngine.SQL.Functions].[Triggers].[SampleTrigger] In this manner, the trigger fires successfully but I'm not able to debug it from VS 2005Can someone help me? ThanksThank you|||

This is a known bug in Visual Studio that was postponed to a later release. You can submit a comment/vote on it here: http://lab.msdn.microsoft.com/ProductFeedback/viewFeedback.aspx?feedbackid=3732fb5d-55b0-4888-a101-090360681c38

There is a pretty easy workaround however to drop/create the trigger yourself to the schema you want using predeployscript.sql and postdeployscript.sql in your Visual Studio Project. This also lets you use SQLCLR debugging from VS.

|||Thanks. Can you supply me an example of postdeploy e predeploy scripts? What should I write in the SQLTrigger attribute? Or should I comment out this attribute?

|||Ok. Here is the solution. Comment the SqlTrigger Attribute. Write the following postdeployscript.sql

USE [TestSQL2k5]

GO

CREATE TRIGGER [Trigger1] ON [MySchema].[Table1] AFTER INSERT AS

EXTERNAL NAME [CM.Library.Blocks.SecurityEngine.SQL.Functions].[Triggers].[Trigger1]

deploy assembly. VS SQLCLR debug works fine

|||

to Haplo_69:
which folder should i save the postdeployscript.sql
when and how should i call the postdeploysript.sql?

i have tested your solution:
1. create a postdeployscript.sql at 'test scripts' folder in project directory
2. in project post-build event command, wrote script to call the postdeployscript.sql
but it failed,

|||

postdeploy.sql and predeploy.sql should be in ypur project root. no script is needed in post-build event

|||

I mean: postdeployscript.sql and predeployscript.sql of course

|||This bug has been fixed in SQL 2005 SP1?|||I have SQL 2005 SP1 installed and deploying from Visual Studio is still not possible. I believe the prolem lies in VS not SQL.|||I have two problems; A simple Insert Trigger.

Problem 1:
If I use the example in MSDN and follow all the direction to the letter
but I get this error and I know it exists!

Cannot find the object "[dbo].[Users]" because it does not exist or you
do not have permissions.

If I remove the attribute
[Microsoft.SqlServer.Server.SqlTrigger(Name = "UserUpdate", Target =
"[dbo].[Users]", Event = "FOR INSERT")]

It compiles okay, I have to your postdeployscript.sql to deploy the
Trigger

Problem 2: Kinda show stopper for me

If I employ above work around using postdeployscript.sql every thing
works, my test scripts fire up the trigger and all the right rows are
affected but no break point is hit in C# trigger code. It just passes
through the breakpoint, NO red circle with exclaimation mark in it! No
errors, but this outout;

Auto-attach to process '[2112] [SQL] entropy' on machine 'entropy'
succeeded.
Debugging script from project script file.

(2 row(s) affected)
(0 row(s) returned)
Finished running sp_executesql.
The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
(0x0).
The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
(0x0).
The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
(0x0).
The thread 'entropy\sqlexpress [54]' (0xe5c) has exited with code 0
(0x0).
The program '[2112] [SQL] entropy: entropy\sqlexpress' has exited with
code 0 (0x0).

If I fire up msvsmon.exe and hit the debug button in VS.NET It says;

31/08/2006 8:50:41 AM ENTROPY\p4r1tyB1t connected.

ANY HELP WOULD BE GREATLY APPRICIATED

System State Information:
Login account is part of Administrator account.
VS.NET 2005 Pro on Core Duo Machine 1 GB Ram
Windows XP SP2
I have set proper permission in DCOM configuration
TCP/IP protocol is enabled, firewalls are not running
I am also a member of sysadmin group
SQL Expresss
CLR Debuggin is enabled
Debug generation is set to full
Assembly is marked as unsafe (It does not matter, same problem even
safe)
Project configuration is debug
Permission in msvsmon.exe is also set to my login account, which is
admin account|||If I remember well, you should be member of sysadmin role on the sql2005 db you are trying to debug

CLR Trigger Error: Could not find Type xxxx in Assembly

I've got the following code (C# code) but when I execute the Create Trigger
statement I get the error
"Could not find Type 'InheritanceTriggers' in assembly 'CLRTriggers'
I believe I'm satisfying all the rules: the class is public, the method is
public, and the method is static.
Anythoughts?
Create Trigger EnforceInheritanceTrigger on [Person]
For INSERT as External Name CLRTriggers.InheritanceTriggers.EnforceBaseObjec
t
GO
CLRTriggers.dll code below
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
namespace My.SQLServer
{
public class InheritanceTriggers
{
[SqlTrigger(Name = @."EnforceInheritanceTrigger", Target =
"[dbo].[Person]", Event = "FOR INSERT")]
public static void EnforceBaseObject()
{
SqlTriggerContext context = SqlContext.TriggerContext;
String SQLForInserted = "SELECT * FROM INSERTED;";
string ATableName = "dbo.Person";
if (context.TriggerAction == TriggerAction.Insert)
{
using (SqlConnection connection
= new SqlConnection(@."context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(SQLForInserted,
connection);
SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
System.Guid id = (System.Guid)reader[0];
string s = <sql statement is here>;
SqlCommand command1 = new SqlCommand(s, connection);
SqlPipe pipe = SqlContext.Pipe;
pipe.Send(s);
}
reader.Close();
}
}
}
}
}
> Create Trigger EnforceInheritanceTrigger on [Person]
> For INSERT as External Name
> CLRTriggers.InheritanceTriggers.EnforceBaseObject
You need to include your namespace:
Create Trigger EnforceInheritanceTrigger on [Person]
For INSERT as External Name
CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
Steven|||Unfortunately, that still didn't help:
Could not find Type 'My.SQLServer.InheritanceTriggers' in assembly
'CLRTriggers'.
but thanks for the suggestion...at least it gave me something new to try. :
)
"Steven Hemingray [MSFT]" wrote:

>
> You need to include your namespace:
> Create Trigger EnforceInheritanceTrigger on [Person]
> For INSERT as External Name
> CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
> Steven
>
>|||I beg your pardon! I got my namespaces mixed up and you are absolutely
correct! Thank you Steve H! (I'll bet you were saying to yourself, "this
guy's nuts", I know that's the problem) :)
"Steven Hemingray [MSFT]" wrote:

>
> You need to include your namespace:
> Create Trigger EnforceInheritanceTrigger on [Person]
> For INSERT as External Name
> CLRTriggers.[My.SQLServer.InheritanceTriggers].EnforceBaseObject
> Steven
>
>

CLR Trigger -> Send Insert/Update/Delete Message to Windows Service

Hi,

I have an c# windows service, which is running on the same machine where my mssql server is installed. This service must be informed for each insert/update/delete event on one specific table.

My idea was to create an CLR Trigger for this table which can communicate with this service via .NET remoting. But the problem is, that the system.runtime.remoting assembly is not integrated within the mssql clr enviroment => i can't use remoting.

Are there any other idea's how can i solve this?

Best regards,
Thomas

Hi,

what about using Query Notification ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||I had the same problem as you and I have used two different tecniques: both of them uses CLR stored procs.

1. An OS event set by SqlServer: useful when to have to notify that an event occurred without any param. Each different event must be attached to a different event name.

[SqlProcedure]
public static void SetOsEvent(SqlString eventName) {
try {
EventWaitHandle.OpenExisting((string)eventName).Set();
}
catch (WaitHandleCannotBeOpenedException) {
}
}

2. An UDP Message. Useful for non-reliable and fast messaging system, such as broadcasting the internal net that an event has occurred.

[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message) {
try {
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
client.Send(datagram, datagram.Length, (string)address, (int)port);
}
catch{
}
}

[SqlProcedure]
public static void UdpBroadcast(SqlInt32 port, SqlString message) {
try {
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
byte[] datagram = message.GetUnicodeBytes();
System.Net.IPEndPoint ep = new System.Net.IPEndPoint(System.Net.IPAddress.Broadcast, (int)port);
client.Send(datagram, datagram.Length, ep);
}
catch {
}
}

Hope that helps.

CLR Trigger

Hi,

I want to call webservice in the Trigger. How to call the webservice from CLR Triggers?.If anybody knows teh detail let me know.

And i worked out the following Example code for CLR Trigger.

using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;

namespace CLR
{
public class Class1
{
public static void InsertTrigger()
{
SqlTriggerContext sqlTrigger = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand cmd = SqlContext.GetCommand();
if (sqlTrigger.TriggerAction == TriggerAction.Insert)
{
cmd.CommandText = "Select * from Inserted";
SqlDataRecord dr = cmd.ExecuteRow();
string Subject_uri = dr[0].ToString();
string predicate = dr[1].ToString();
string Obj = dr[2].ToString();
sqlPipe.Execute(cmd);
sqlPipe.Send("You Selected Subject_uri ::"+Subject_uri+" Predicate ::"+predicate+" Object ::"+Obj);
}
}

}
}

After that i created Assembly and Trigger and clr enabled

create assembly CLR from 'C:\Inetpub\wwwroot\Vinoth\CLR\bin\Debug\CLR.dll'

CREATE TRIGGER InsertTrigger
ON Triplets
For Insert
As
External Name
CLR.[CLR.Class1].InsertTrigger

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'clr enabled', 1
RECONFIGURE

insert into Triplets values('test','_2','Testing1')

When i insert the values it is inserting into table .But why ican't get sqlpipe.send Message.
How do i know whether my CLR trigger is working?
Thanks,
VinothMy entire body just shuddered! You want to call a Web Service in a database trigger? Do you realize what the performance hit will be for that?|||

To answer the specific question: pipe.Send(string) is the equivalent of "PRINT" in T-SQL. In order to "see" the string you sent from the server in the client you need to be listening to info-messages.

If you're using System.Data.SqlClient in the client, the way to do this is to subscribe to the InfoMessage event in the SqlConnection object. If you're trying this from Query Analyzer or Management Studio, then you'll see it in the "messages" tab after executing the query.

I have to extra comments:
1. Note that when you move to newer CTPs or Beta versions (or the RTM version) you'll have to update your code as we no longer have a separate System.Data.SqlServer namespace for inproc data-access. That capability has been integrated into the provider in System.Data.SqlClient.

2. Doing network I/O -particularly over the internet- inside a trigger can seriously impact the performance of your database. I can't say "don't do it" because I don't have understanding of your particular scenario, so let me say this: I recommend that you do this only if you really need to and understand the implications of doing so.

Hope this helps.

|||I don't think if there is any performance related problem because of using web services as far as it takes place in another thread. After all Notification services do the similar thing.|||

What makes you think that the Web Services call takes place on another thread?

|||

Web services calls don't take place on another thread. The application code can explicitly create a working thread and call any time-consuming tasks or blocking calls on that thread.

From the other hand I'm not sure even calling the "time-consuming tasks or blocking calls" directly from the triggered function causing performance hits, because I guess Sql Server engine is smart enough to call the callbacks (subscribed trigger methods) asynchronously (multithread call) not sequentially.

|||I think you will probably find that SQL Server will prevent new threads being created.

Can you imagine if a certain trigger created a new thread everytime it ran, and then a table was updated a 1000 times in a row? This would soon bring down the server.|||Have you tried that before?|||

By default, the assembly is installed with only permission to execute "safe" code. The CLR integration has the notion of three levels of execution permissions; these are enforced on the assembly level:

SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. EXTERNAL: Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code. UNSAFE: Access is not limited whatsoever.|||Just because you can do it, doesn't make it a good idea.|||

hi,

i am also doing the same type of thing.

i made a Dll in c#2005, which is consuming a Java Based web service.

then, i registered that Assembly within sql server 2005. then i created a function which use that Assmbly. then i created a stored procedure which is using that function.

Now, the Issue is the performance. if i hit the webservice from the windows Based Application. it works very fine.

but from Sql Server its performance is worst.

since Stored procedure is called by a Bill payment System. so this procedure receives more than 30000 calls per day. But most of the time the assmbly gets failed.

sometime i receive Error Like:

Can't load Assembly....

and sometime i receive error like:

The underlying connection was closed: An unexpected error occurred on a receive. .......System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond....

Anyone can help me to improve this performance issue.

Regards,

Vineet

CLR Trigger

Hi,

I want to call webservice in the Trigger. How to call the webservice from CLR Triggers?.If anybody knows teh detail let me know.

And i worked out the following Example code for CLR Trigger.

using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;

namespace CLR
{
public class Class1
{
public static void InsertTrigger()
{
SqlTriggerContext sqlTrigger = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand cmd = SqlContext.GetCommand();
if (sqlTrigger.TriggerAction == TriggerAction.Insert)
{
cmd.CommandText = "Select * from Inserted";
SqlDataRecord dr = cmd.ExecuteRow();
string Subject_uri = dr[0].ToString();
string predicate = dr[1].ToString();
string Obj = dr[2].ToString();
sqlPipe.Execute(cmd);
sqlPipe.Send("You Selected Subject_uri ::"+Subject_uri+" Predicate ::"+predicate+" Object ::"+Obj);
}
}

}
}

After that i created Assembly and Trigger and clr enabled

create assembly CLR from 'C:\Inetpub\wwwroot\Vinoth\CLR\bin\Debug\CLR.dll'

CREATE TRIGGER InsertTrigger
ON Triplets
For Insert
As
External Name
CLR.[CLR.Class1].InsertTrigger

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'clr enabled', 1
RECONFIGURE

insert into Triplets values('test','_2','Testing1')

When i insert the values it is inserting into table .But why ican't get sqlpipe.send Message.
How do i know whether my CLR trigger is working?
Thanks,
VinothMy entire body just shuddered! You want to call a Web Service in a database trigger? Do you realize what the performance hit will be for that?|||

To answer the specific question: pipe.Send(string) is the equivalent of "PRINT" in T-SQL. In order to "see" the string you sent from the server in the client you need to be listening to info-messages.

If you're using System.Data.SqlClient in the client, the way to do this is to subscribe to the InfoMessage event in the SqlConnection object. If you're trying this from Query Analyzer or Management Studio, then you'll see it in the "messages" tab after executing the query.

I have to extra comments:
1. Note that when you move to newer CTPs or Beta versions (or the RTM version) you'll have to update your code as we no longer have a separate System.Data.SqlServer namespace for inproc data-access. That capability has been integrated into the provider in System.Data.SqlClient.

2. Doing network I/O -particularly over the internet- inside a trigger can seriously impact the performance of your database. I can't say "don't do it" because I don't have understanding of your particular scenario, so let me say this: I recommend that you do this only if you really need to and understand the implications of doing so.

Hope this helps.

|||I don't think if there is any performance related problem because of using web services as far as it takes place in another thread. After all Notification services do the similar thing.|||

What makes you think that the Web Services call takes place on another thread?

|||

Web services calls don't take place on another thread. The application code can explicitly create a working thread and call any time-consuming tasks or blocking calls on that thread.

From the other hand I'm not sure even calling the "time-consuming tasks or blocking calls" directly from the triggered function causing performance hits, because I guess Sql Server engine is smart enough to call the callbacks (subscribed trigger methods) asynchronously (multithread call) not sequentially.

|||I think you will probably find that SQL Server will prevent new threads being created.

Can you imagine if a certain trigger created a new thread everytime it ran, and then a table was updated a 1000 times in a row? This would soon bring down the server.|||Have you tried that before?|||

By default, the assembly is installed with only permission to execute "safe" code. The CLR integration has the notion of three levels of execution permissions; these are enforced on the assembly level:

SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. EXTERNAL: Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code. UNSAFE: Access is not limited whatsoever.|||Just because you can do it, doesn't make it a good idea.|||

hi,

i am also doing the same type of thing.

i made a Dll in c#2005, which is consuming a Java Based web service.

then, i registered that Assembly within sql server 2005. then i created a function which use that Assmbly. then i created a stored procedure which is using that function.

Now, the Issue is the performance. if i hit the webservice from the windows Based Application. it works very fine.

but from Sql Server its performance is worst.

since Stored procedure is called by a Bill payment System. so this procedure receives more than 30000 calls per day. But most of the time the assmbly gets failed.

sometime i receive Error Like:

Can't load Assembly....

and sometime i receive error like:

The underlying connection was closed: An unexpected error occurred on a receive. .......System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond....

Anyone can help me to improve this performance issue.

Regards,

Vineet

|||

David M. Kean - MSFT wrote:

My entire body just shuddered! You want to call a Web Service in a database trigger? Do you realize what the performance hit will be for that?

I have a simiar scenario where I need to call a webservice from a trigger. The scenario is, I need to generate a report using reporting services everytime a record gets inserted into a table. We have one working fairly well now, unless an exception is thrown. Is there anyway to create a fire and forget trigger, or subscribe to an event? I don't want the report generation to slow down the inserting of the record, even if there is an exception thrown. I also want to avoid table polling, or creating a scheduled SSIS orchestration. What would you recommend in my scenario?

-Forced to be the company DBA

|||cjarvis,

Use Service Broker. Fire a trigger that sends a message to a service. the queue which the service is built upon then has an activation procedure which does all the report generating stuff. The upside with this is that when the trigger has fired and the message been sent, the activation proc executes on a separate thread.

Niels

CLR Trigger

Hi,

I want to call webservice in the Trigger. How to call the webservice from CLR Triggers?.If anybody knows teh detail let me know.

And i worked out the following Example code for CLR Trigger.

using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlServer;

namespace CLR
{
public class Class1
{
public static void InsertTrigger()
{
SqlTriggerContext sqlTrigger = SqlContext.GetTriggerContext();
SqlPipe sqlPipe = SqlContext.GetPipe();
SqlCommand cmd = SqlContext.GetCommand();
if (sqlTrigger.TriggerAction == TriggerAction.Insert)
{
cmd.CommandText = "Select * from Inserted";
SqlDataRecord dr = cmd.ExecuteRow();
string Subject_uri = dr[0].ToString();
string predicate = dr[1].ToString();
string Obj = dr[2].ToString();
sqlPipe.Execute(cmd);
sqlPipe.Send("You Selected Subject_uri ::"+Subject_uri+" Predicate ::"+predicate+" Object ::"+Obj);
}
}

}
}

After that i created Assembly and Trigger and clr enabled

create assembly CLR from 'C:\Inetpub\wwwroot\Vinoth\CLR\bin\Debug\CLR.dll'

CREATE TRIGGER InsertTrigger
ON Triplets
For Insert
As
External Name
CLR.[CLR.Class1].InsertTrigger

sp_configure 'show advanced options', 1
RECONFIGURE
sp_configure 'clr enabled', 1
RECONFIGURE

insert into Triplets values('test','_2','Testing1')

When i insert the values it is inserting into table .But why ican't get sqlpipe.send Message.
How do i know whether my CLR trigger is working?
Thanks,
VinothMy entire body just shuddered! You want to call a Web Service in a database trigger? Do you realize what the performance hit will be for that?|||

To answer the specific question: pipe.Send(string) is the equivalent of "PRINT" in T-SQL. In order to "see" the string you sent from the server in the client you need to be listening to info-messages.

If you're using System.Data.SqlClient in the client, the way to do this is to subscribe to the InfoMessage event in the SqlConnection object. If you're trying this from Query Analyzer or Management Studio, then you'll see it in the "messages" tab after executing the query.

I have to extra comments:
1. Note that when you move to newer CTPs or Beta versions (or the RTM version) you'll have to update your code as we no longer have a separate System.Data.SqlServer namespace for inproc data-access. That capability has been integrated into the provider in System.Data.SqlClient.

2. Doing network I/O -particularly over the internet- inside a trigger can seriously impact the performance of your database. I can't say "don't do it" because I don't have understanding of your particular scenario, so let me say this: I recommend that you do this only if you really need to and understand the implications of doing so.

Hope this helps.

|||I don't think if there is any performance related problem because of using web services as far as it takes place in another thread. After all Notification services do the similar thing.|||

What makes you think that the Web Services call takes place on another thread?

|||

Web services calls don't take place on another thread. The application code can explicitly create a working thread and call any time-consuming tasks or blocking calls on that thread.

From the other hand I'm not sure even calling the "time-consuming tasks or blocking calls" directly from the triggered function causing performance hits, because I guess Sql Server engine is smart enough to call the callbacks (subscribed trigger methods) asynchronously (multithread call) not sequentially.

|||I think you will probably find that SQL Server will prevent new threads being created.

Can you imagine if a certain trigger created a new thread everytime it ran, and then a table was updated a 1000 times in a row? This would soon bring down the server.|||Have you tried that before?|||

By default, the assembly is installed with only permission to execute "safe" code. The CLR integration has the notion of three levels of execution permissions; these are enforced on the assembly level:

SAFE: Access only to CLR code. No access is allowed to external resources, thread management, unsafe code or interop. EXTERNAL: Access is allowed to external systems, such as the EventLog, File System, and network. Still no access to unsafe code or interop code. UNSAFE: Access is not limited whatsoever.|||Just because you can do it, doesn't make it a good idea.|||

hi,

i am also doing the same type of thing.

i made a Dll in c#2005, which is consuming a Java Based web service.

then, i registered that Assembly within sql server 2005. then i created a function which use that Assmbly. then i created a stored procedure which is using that function.

Now, the Issue is the performance. if i hit the webservice from the windows Based Application. it works very fine.

but from Sql Server its performance is worst.

since Stored procedure is called by a Bill payment System. so this procedure receives more than 30000 calls per day. But most of the time the assmbly gets failed.

sometime i receive Error Like:

Can't load Assembly....

and sometime i receive error like:

The underlying connection was closed: An unexpected error occurred on a receive. .......System.IO.IOException: Unable to read data from the transport connection: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond....

Anyone can help me to improve this performance issue.

Regards,

Vineet