May 7, 2008 at 9:03 am
I have a lookup table.
RefType EngCode EngDesc
----------------------------------------
FeeType Hand HandelingCharge
FeeType Pack PackagingCharge
PayType CC CreditCard
PayType CK Check
There are many RefTypes (Fees,Charges,Accs etc.)
The EngDesc is the Description of each item and the EngCode is the abbrieviation.
I am attempting to write a Check Constraint that will enforce unique EngCodes for one paticular RefType.
Is this possible with a Check Constraint or must I use a Trigger?
So far I've written this:
EngCode <> dbo.fnFeeTypeAbrvCheck(EngCode) and RefType = 'FeeType'
This does enforce unique EngCodes but it also only allows 'FeeType' for the RefType so it's not working the way I need it to.
May 7, 2008 at 2:06 pm
Hello,
Why not create a unique combo index?
For example:
RefType EngCode EngDesc
----------------------------------------
FeeType Hand HandelingCharge
FeeType Pack PackagingCharge
PayType CC CreditCard
PayType CK Check
create table tlb_test
( tbl_test_id int identity,
RefType varchar(20) null,
EngCode varchar(20) null,
EngDesc varchar(40) null )
create unique nonclustered index tlb_test_refeng
on tlb_test ( RefType, EngCode )
insert tlb_test
select 'FeeType', 'Hand', 'HandleingCharge' union all
select 'FeeType', 'Pack', 'PackagingCharge' union all
select 'PayType', 'CC', 'CreditCard' union all
select 'PayType', 'CK', 'Check'
select * from tlb_test
tbl_test_id RefType EngCode EngDesc
----------- -------------------- -------------------- ----------------------------------------
1 FeeType Hand HandleingCharge
2 FeeType Pack PackagingCharge
3 PayType CC CreditCard
4 PayType CK Check
(4 row(s) affected)
insert tlb_test
select 'Feetype', 'Hand', 'The other hand'
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'tlb_test' with unique index 'tlb_test_refeng'.
The statement has been terminated.
This will enforce the RefType and EngCode rule.
Regards,
Terry
May 7, 2008 at 8:41 pm
It's better to have it as a constraint:
create table tlb_test (
id int identity(1,1), -- if you need it ???
RefType varchar(20) null,
EngCode varchar(20) null,
EngDesc varchar(40) null,
CONSTRAINT PK_tlb_test PRIMARY KEY ( id ),
CONSTRAINT UC_tlb_test UNIQUE ( RefType, EngCode )
)
_____________
Code for TallyGenerator
May 8, 2008 at 5:21 am
Serigy,
Thanks for the reply everyone.
The problem is this unqiue data enforcement only applies to one RefType in the table, not all of them. If I understand Unique Constraints correctly what you suggest will enforce uniqueness for all RefType,EngCode combinations. In my case only RefType = 'FeeType' should have unique EngCodes when compared to other 'FeeType' EngCodes.
I wanted to know if you could limit a constraint to only certain data in a table instead of all the data. Does that make sense? Let me know if I need to clarify. Thanks.
May 8, 2008 at 5:24 am
tbeadle,
Thanks for the reply. I never thought of a unique combo index. I guess that would work the same as a constraint though? Wouldn't it?
In this case the uniquness only needs to apply to one RefType and that is 'FeeType'. All 'FeeType' EngCodes have to be unique. The rest of the data doesn't matter in the table.
May 9, 2008 at 9:24 am
If you need to determine what to do based on the value of a particular row against that of another row in the same table, a Trigger is your only method if you do not want to use Unique constraints (which would check the value in ALL rows for uniqueness).
Toni
** Edited to add Update tests **
if exists(select name from sysobjects where name = 't' and type='U')
drop table t
/* Build a test table */
create table t (a int, b int, c varchar(5))
insert into t (a,b,c)
select 1,1,'a' union all
select 2,2,'b' union all
select 3,3, 'g' union all
select 4,4,'77'
select * from t
if exists(select name from sysobjects where name = 't1' and type='TR')
drop trigger t1
go
/* Build the trigger */
create trigger t1 on T instead of insert, update
/* Sample trigger to handle single record */
/* Would need to be modified to handle multiple rows */
as
if update(a)
begin
if ((select count(a) from inserted) > 1)
begin
RAISERROR
('More than 1 row can not be handled.',
16, 1)
-- rollback transaction
return
end
declare @newval int
select @newval = (select a from inserted)
if @newval in (select a from t)
begin
RAISERROR
('Duplicate value for a is %d.',
16, 1, @newval)
-- rollback transaction
return
end
end
if exists (select * from deleted)
begin
declare @a int
declare @b-2 int
declare @C varchar(5)
select @a = (select a from deleted)
select @b-2 = (select b from deleted)
select @C = (select c from deleted)
RAISERROR
('A is %d B is %d C is %s.',
delete t where a=@a and b=@b and c=@c
end
insert into t
select * from inserted
go
/* Test it out */
insert into t -- works
values (6,3,'f')
insert into t -- doesn't work
values (3,3,'f')
insert into t
select 7,1,'a' union all
select 8,1,'a'
select * from t
update t
set b=2 where a=4
select * from t
update t set a=2 where b=3
update t set a=2 where a=6
update t set a=8 where a =6
select * from t
May 9, 2008 at 9:32 am
Thanks for the reply. I used a trigger to handle this issue but it would have been cool if I could do it with a constraint.
I guess that's a limitation of a constraint. It applies to all the data.
May 9, 2008 at 2:56 pm
AVB (5/9/2008)I guess that's a limitation of a constraint. It applies to all the data.
I guess that's a limitation of a relational database.
Data must be consistent.
_____________
Code for TallyGenerator
May 9, 2008 at 7:42 pm
Here are some cleaner versions of the triggers that would handle multiple rows in an Insert/Update. I had to add an Identity column (unless you have a unique key) in order to handle the Updates correctly.
Not that anyone will read this but I wanted to make it right and hey.. it's Friday night. Why not go crazy!
Toni
INSERT TRIGGER:
if exists(select name from sysobjects where name ='insertt' and type='tr')
drop trigger insertt
/**** CREATE THE INSERT TRIGGER **/
GO
create trigger insertt on T instead of insert
/* Trigger to prevent insert a value for B if one already exists*/
as
if update(a)
begin
if exists(select b from inserted where b=2) and exists (select b from t where b=2)
begin
RAISERROR
('Duplicate value of column B Can Not Be Inserted',
16, 1)
-- rollback transaction
return
end
end
insert t (a,b,c)
select a,b,c from inserted
go
UPDATE TRIGGER:
/* Build the Update trigger */
if exists(select name from sysobjects where name ='updatet' and type='tr')
drop trigger updatet
/**** CREATE THE UPDATE TRIGGER **/
GO
create trigger updatet on T instead of update
/* Update trigger to prevent Having 2 records with Value of 2 in column b
*and each record has different values for column a
*/
as
if update(a) or update(b)
begin
if exists(select i.b from inserted i
join t on i.id<> t.id and i.b=2)
begin
RAISERROR
('Duplicate value of column B for column A is not allowed.',
16, 1)
-- rollback transaction
return
end
end
Update t
set t.a=i.a,t.b=i.b,t.c=i.c
from t
join inserted i on t.id=i.id
go
May 11, 2008 at 5:27 am
Hai AVB,
TRy out this check constraint , Hope this will suit your requirement make slight changes if necessary
create table tlb_test
( tbl_test_id int identity,
RefType varchar(20) null,
EngCode varchar(20) null,
EngDesc varchar(40) null )
go
--create unique nonclustered index tlb_test_refeng
--on tlb_test ( RefType, EngCode )
insert tlb_test
select 'FeeType', 'Hand', 'HandleingCharge' union all
select 'FeeType', 'Pack', 'PackagingCharge' union all
select 'PayType', 'CC', 'CreditCard' union all
select 'PayType', 'CK', 'Check'
go
select * from tlb_test
CREATE FUNCTION FeeTypecnt(@FeeType VARCHAR(20),@EngCode VARCHAR(20))
returns int
as
begin
declare @cnt int
set @cnt=(select count(EngCode) from tlb_test where RefType=@FeeType and EngCode=@EngCode)
return @cnt
end
ALTER TABLE [dbo].[tlb_test] with nocheck
add CONSTRAINT [CK_tlb_test_countcheck] CHECK
((dbo.FeeTypecnt(RefType,EngCode)=0))
Now if you try this
insert tlb_test
select 'Feetype', 'Hand', 'The other hand'
Regards,
Rajesh
May 11, 2008 at 11:52 am
I wouldn't bother with a trigger, or some function in a trigger. This can easily be handled using an indexed view and a unique clustered index on the indexed view.
Create Table dbo.Unique_Test
( test_id int identity,
RefType varchar(20) null,
EngCode varchar(20) null,
EngDesc varchar(40) null )
Go
Insert dbo.unique_test
Select 'FeeType', 'Hand', 'HandleingCharge'
Union All Select 'FeeType', 'Pack', 'PackagingCharge'
Union All Select 'PayType', 'CC', 'CreditCard'
Union All Select 'PayType', 'CK', 'Check'
Go
Create View dbo.vUnique_FeeType With SCHEMABINDING As
Select test_id, reftype, engcode, engdesc From dbo.Unique_Test Where Reftype = 'FeeType'
Go
Create Unique Clustered Index UI_vUnique_Test_FeeType On dbo.vUnique_FeeType (Reftype, EngCode);
Go
Select * From dbo.vUnique_FeeType;
Insert Into Unique_Test Values('FeeType', 'Hand', 'New Test'); -- fails
Insert Into Unique_Test Values('FeeType', 'Hand2', 'Testing'); -- succeeds
Insert Into Unique_Test Values('PayType', 'CK', 'Checking'); -- succeeds
Insert Into Unique_Test Values('PayType', 'CK', 'Checking Again'); -- succeeds
Insert Into Unique_Test Values('FeeType', 'Hand2', 'Second Test'); -- fails
Select * From dbo.Unique_Test;
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 12, 2008 at 6:47 am
toniupstny,
Thanks for the code. I actually used a trigger last week. Here is my code:
ALTER TRIGGER [dbo].[trg_FeeTypeAbrvCheck] ON [dbo].[t_XREF]
FOR UPDATE,INSERT
as
DECLARE @Ref_Type varchar(20),
@Eng_Code varchar(11),
@Eng_ItemDesc varchar (50),
@CheckBit tinyint,
@ID int,
SET NOCOUNT ON
IF UPDATE(Eng_Code)
BEGIN
select @Ref_Type = Ref_Type,@Eng_Code = Eng_Code,@ID = ID,@Eng_ItemDesc = Eng_ItemDesc from Inserted
--First Check if FEETYPE
IF @Ref_Type = 'FeeType'
BEGIN
--Ref_Type,Eng_Code and Eng_ItemDesc need to be supplied. Check for all of them
IF isnull(@Eng_Code,'') = '' or isnull(@ENg_ItemDesc,'') = ''
BEGIN
PRINT 'All required values weren''t supplied. Rollingback Tran...'
ROLLBACK TRAN
RETURN
END
--Check for Valid Length
IF len(@Eng_Code) <> 3
BEGIN
PRINT 'EngCode is not 3 chars. Terminating and Rolling Back Tran.'
ROLLBACK TRAN
RETURN
END
IF len(@Eng_Code) = 3
BEGIN
PRINT 'EngCode = 3. Checking for duplicate data....'
select @CheckBit = Count(*) from t_XREF where Eng_Code = @Eng_Code and ID <> @ID and Ref_Type ='FeeType'
IF @CheckBit > 0
BEGIN
PRINT 'Data is not unique. Rolling Back Tran...'
ROLLBACK TRAN
RETURN
END
IF isnull(@CheckBit,0) = 0
BEGIN PRINT 'Data is unique. Continue Update...' END
END
END
END
May 12, 2008 at 5:34 pm
AVB, what if somebody will insert or update more than 1 row?
Any error in further rows will happily bypass your check and end up in the table.
And don't tell me nobody has access to your productio database and the only way to change data in your table is via single SP which can update only one row at a time. Because we all know it is not true.
Then, in your trigger you use ROLLBACK TRAN. Do you realize it will roll back not only insert/update, but any transaction declared and not committed yet within the batch?
And you included value from column Eng_ItemDesc in your validation but you don't check if that clolumn was actually updated - hole in the logic.
This is how your trigger should look like:
ALTER TRIGGER [dbo].[trg_FeeTypeAbrvCheck] ON [dbo].[t_XREF]
FOR UPDATE,INSERT
as
SET NOCOUNT ON
IF UPDATE(Eng_Code) OR UPDATE(Eng_ItemDesc)
BEGIN
IF EXISTS (select 1 from inserted -- check all "Fee Type" values in table "inserted"
where Ref_Type = 'FeeType'
and (Eng_Code = '' OR Eng_Code IS NULL Eng_ItemDesc = '' OR Eng_ItemDesc IS NULL )
RAISERROR ( 'All required values for Ref_Type "FeeType" weren''t supplied.' , 16, 1 )
/*RAISERROR within a trigger rollbacks INSERT/UPDATE transaction immediately and returns the error message
Error can be caught by outside process performing INSERT/UPDATE with help from system variable @@ERROR
*/
IF EXISTS (select 1 from inserted
where len(Eng_Code) <> 3 )
RAISERROR ( 'EngCode is not 3 chars. Transaction is rolled back.' , 16, 1 )
IF EXISTS (select 1 from t_XREF
-- check the data in destination table after INSERT/UPDATE is performed
where Ref_Type = 'FeeType'
GROUP BY Eng_Code
HAVING COUNT(*) > 1 )
RAISERROR ( 'Data for Ref_Type "FeeType" is not unique. Transaction is rolled back.' , 16, 1 )
END
_____________
Code for TallyGenerator
May 13, 2008 at 7:08 am
Sergiy. Your post was quite interesting.
I tried unsuccessfully to look up under what conditions a Raiserror would cause the trigger to cancel the Insert or Update statements without an explicit ROLLBACK statement. Did I read your post incorrectly or is there somewhere you can point me too for further research into how this works?
Thank you
Toni
May 13, 2008 at 7:30 am
Error with severity level 16 raised within a transaction (trigger is always within transaction) will cause rolling it back.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply