BizRule implementation

I have a table with follwoing feilds:
1)ClientID
2)EmailAddress
3)AddressDescription
4)Primary
5)BizRuleFlag
there could be multiple address for each client ,but each client **MUST**
have atleast one address with "Primary" flag set to "Y" (which mean that one
is his Primary address) and also one of the addresses **Must** also contain
an EmailAddress.I'd like to check all the data in the table and if these
rules are met set the "BizRuleFlag" to 1 otherwise to 0.
Any helps?
Thanks a lotHi
I am not sure why you are setting the bizrule flag as it would be out of
date when then data changes. Adding the check in a trigger to update all the
rows will have a large overhead.
You may want to add an addressid column to your table so that you can
identify rows uniquely or make some other column combination the PK.
Primary for a column name may prove tedious as you will always need to
enquote it.
If your rule was that the primary address has to have an email then you can
add contraints that will do this. If does not stop them not adding a primary
address through e.g.
CREATE TABLE Addresses (
ClientID INT NOT NULL,
AddressID INT NOT NULL,
EmailAddress varchar(50),
AddressDescription varchar(50) NOT NULL,
[Primary] bit not null default 0,
BizRuleFlag bit not null default 0,
CONSTRAINT PK_Addresses PRIMARY KEY (ClientId, AddressId) ,
CONSTRAINT CK_Primary_EMAIL CHECK ([Primary] = 0 OR ([Primary] = 1 AND
EmailAddress IS NOT NULL))
)
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 1, NULL, 'address 1', DEFAULT, DEFAULT )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 2, NULL, 'address 2', 1, DEFAULT )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CK_Primary_EMAIL'.
The conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 3, 'Address With Email', 'address 3', 1, DEFAULT )
Adding constraints so that and email must exist and a primary must exist can
be done using functions, although they may have a significant impact on
performance.
You will also need to add a Primary address first with an email, but
subsequent changes can mean that they are not bound together.
e.g.
CREATE FUNCTION fnPrimaryExists (@.ClientId Int, @.addressId int)
RETURNS tinyint
AS
BEGIN
RETURN CASE WHEN EXISTS ( SELECT * FROM Addresses WHERE ClientId =
@.ClientId AND AddressId <> @.AddressID AND [Primary] = 1) THEN 1 ELSE 0 END
END
CREATE FUNCTION fnEmailExists (@.ClientId Int)
RETURNS tinyint
AS
BEGIN
RETURN CASE WHEN EXISTS ( SELECT * FROM Addresses WHERE ClientId =
@.ClientId AND [EMAILAddress] IS NOT NULL) THEN 1 ELSE 0 END
END
CREATE TABLE Addresses (
ClientID INT NOT NULL,
AddressID INT NOT NULL,
EmailAddress varchar(50),
AddressDescription varchar(50) NOT NULL,
[Primary] bit not null default 0,
BizRuleFlag bit not null default 0,
CONSTRAINT PK_Addresses PRIMARY KEY (ClientId, AddressId) ,
CONSTRAINT CK_Primary CHECK (([Primary] = 1 AND dbo.fnPrimaryExists
(ClientId, AddressId ) = 0) OR ([Primary] = 0 AND dbo.fnPrimaryExists
(ClientId, AddressId ) = 1)),
CONSTRAINT CK_Email CHECK (EmailAddress IS NOT NULL OR dbo.fnEmailExists
(ClientId) = 1)
)
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 1, NULL, 'address 1', DEFAULT, DEFAULT )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CK_Primary'. The
conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 2, NULL, 'address 2', 1, DEFAULT )
/*
Server: Msg 547, Level 16, State 1, Line 1
INSERT statement conflicted with TABLE CHECK constraint 'CK_Email'. The
conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 3, 'Address With Email', 'address 3', 1, DEFAULT )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary], BizRuleFlag )
VALUES ( 1, 4, 'Address With Email', 'address 4', DEFAULT, DEFAULT )
UPDATE Addresses SET EmailAddress = NULL
where CLIENTID = 1 and AddressId = 3
UPDATE Addresses SET [PRIMARY] = 0
where CLIENTID = 1 and AddressId = 3
/*
Server: Msg 547, Level 16, State 1, Line 1
UPDATE statement conflicted with TABLE CHECK constraint 'CK_Primary'. The
conflict occurred in database 'Test', table 'Addresses'.
The statement has been terminated.
*/
You may want to make bizrule a computed column
CREATE TABLE Addresses (
ClientID INT NOT NULL,
AddressID INT NOT NULL,
EmailAddress varchar(50),
AddressDescription varchar(50) NOT NULL,
[Primary] bit not null default 0,
BizRuleFlag AS CASE WHEN [Primary] = 1 OR [EMAILAddress] IS NOT NULL THEN 1
ELSE 0 END,
CONSTRAINT PK_Addresses PRIMARY KEY (ClientId, AddressId)
)
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 1, NULL, 'address 1', DEFAULT )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 2, NULL, 'address 2', 1 )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 3, 'Address With Email', 'address 3', 1 )
INSERT INTO Addresses (ClientID, AddressID, EmailAddress,
AddressDescription, [Primary] )
VALUES ( 1, 4, 'Address With Email', 'address 4', DEFAULT )
SELECT * FROM Addresses
HTH
John
"J-T" wrote:

> I have a table with follwoing feilds:
> 1)ClientID
> 2)EmailAddress
> 3)AddressDescription
> 4)Primary
> 5)BizRuleFlag
> there could be multiple address for each client ,but each client **MUST**
> have atleast one address with "Primary" flag set to "Y" (which mean that o
ne
> is his Primary address) and also one of the addresses **Must** also contai
n
> an EmailAddress.I'd like to check all the data in the table and if these
> rules are met set the "BizRuleFlag" to 1 otherwise to 0.
> Any helps?
> Thanks a lot
>
>