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_PostalCodeMinimumCharacters]  DEFAULT ((0)),
    [PostalCodeMaximumCharacters] [int] NOT NULL CONSTRAINT
[DF_Countries_PostalCodeMaximumCharacters]  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_Trigger", 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_CountryCode";
                            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.

Show quoteHide quote

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 own
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.

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, 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)
        {
            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')

SQL script, split digits and letters
Concatenating:www.aspfaq.com/show.asp?id=2529 Is the glass half full or half empty or..?
Query help - resultset too large... impossible!
Used Disk Space reporting and sending Email
Query assistance or advice