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')
fatthippo:
