q; duplicate dhcek on two columns

Hello,
I have a table T1 and on this table I have an insert trigger. In the trigger
I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or no
t
(duplicate dhcek on two columns), if yes return error from trigger. There
might be T1.ID and T1.Type=’abc’ duplicated, that is fine.I guess I'm a bit confused about what you are attempting to accomplish.
It seems that you allow some duplicate values ('abc') between the two
columns-but not others. (If it was a simple as no duplicate values, then a
PK would work.)
Is it that a new row cannot have both t1.ID AND t1.Type = 'xyz',
OR
A new row can have a t1.ID or t1.Type = 'xyz' IF another row has either but
not both,
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values of either t1.ID or t1.Type = 'xyz',
OR
A new row cannot have both t1.ID and t1.Type = 'xyz' IF there is another row
with values for t1.ID AND t1.Type = 'xyz',
OR ...
Is it only the value 'xyz' that is of concern?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:A17263B7-72E0-46D7-8431-FECFC522C810@.microsoft.com...
> Hello,
> I have a table T1 and on this table I have an insert trigger. In the
> trigger
> I need to check if T1.ID and T1.Type='xyz' together are duplicated or not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type='abc' duplicated, that is fine.
>|||Hi
Why not have a unique index on these two columns?
Adapted from http://www.sommarskog.se/error-hand...#triggercontext
Try the following code in your trigger:
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
END
John
"JIM.H." wrote:

> Hello,
> I have a table T1 and on this table I have an insert trigger. In the trigg
er
> I need to check if T1.ID and T1.Type=’xyz’ together are duplicated or
not
> (duplicate dhcek on two columns), if yes return error from trigger. There
> might be T1.ID and T1.Type=’abc’ duplicated, that is fine.
>|||On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:

>Hi
>Why not have a unique index on these two columns?
Hi John,
Agreed 100%.

>Adapted from http://www.sommarskog.se/error-hand...#triggercontext
>Try the following code in your trigger:
>IF EXISTS (SELECT *
> FROM inserted i
> JOIN T1 T ON i.id = T1.id AND i.type = T1.type
> )
>BEGIN
> ROLLBACK TRANSACTION
> RAISERROR('Duplicate Values Entered.', 16, 1)
> RETURN
>END
But this won't work. In an AFTER trigger, this will always result in
error since the new rows are already in the table once the trigger
fires. And in an INSTEAD OF trigger, this will catch the addition of a
second row if the first is already there, but it will miss the insertion
of two duplicate rows in a single INSERT execution.
For an AFTER trigger, this would probably work (untested, though):
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
probably won't work for him.
"check if T1.ID and T1.Type='xyz' together are duplicated"
AND
"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP|||Hi Arnie
That is not the way I interpreted the post, but the post is not well
defined. Posting DDL, sample data and example statements that would work and
would not work would have been better!
John
"Arnie Rowland" wrote:

> Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
> probably won't work for him.
> "check if T1.ID and T1.Type='xyz' together are duplicated"
> AND
> "There might be T1.ID and T1.Type='abc' duplicated, that is fine."
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
> news:dhdcm2db3ivg15dn2fsfifep0kueih1qv1@.
4ax.com...
>
>|||Thanks Hugo, unique constraints would not work since a specific value should
not be repeated not all. I would go with your second suggestion.
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 10:51:01 -0800, John Bell wrote:
>
> Hi John,
> Agreed 100%.
>
> But this won't work. In an AFTER trigger, this will always result in
> error since the new rows are already in the table once the trigger
> fires. And in an INSTEAD OF trigger, this will catch the addition of a
> second row if the first is already there, but it will miss the insertion
> of two duplicate rows in a single INSERT execution.
> For an AFTER trigger, this would probably work (untested, though):
> IF EXISTS (SELECT *
> FROM inserted AS i
> INNER JOIN <BaseTable> AS b
> ON i.id = b.id
> AND i.type = b.type
> GROUP BY b.id, b.type
> HAVING COUNT(*) > 1)
> BEGIN;
> ROLLBACK TRANSACTION;
> RAISERROR('Duplicate Values Entered.', 16, 1);
> RETURN;
> END;
> --
> Hugo Kornelis, SQL Server MVP
>|||Hi
Without examples it is still ambiguous what you are trying to do. A compound
unique constraint on the two columns would have the same effect as the sampl
e
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Thanks Hugo, unique constraints would not work since a specific value shou
ld
> not be repeated not all. I would go with your second suggestion.
> "Hugo Kornelis" wrote:
>|||On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:

>Unless the OP has misstated his scenario, I think that a UNIQUE CONSTRAINT
>probably won't work for him.
>"check if T1.ID and T1.Type='xyz' together are duplicated"
>AND
>"There might be T1.ID and T1.Type='abc' duplicated, that is fine."
Hi Arnie,
You're right - I missed that part of Jim's message.
If this is indeed the requirement (Jim's post, as already pointed out by
several others, is not entirely clear), he could use a trigger like
this:
IF EXISTS (SELECT *
FROM inserted AS i
INNER JOIN <BaseTable> AS b
ON i.id = b.id
AND i.type = b.type
WHERE i.type = 'xyz'
GROUP BY b.id, b.type
HAVING COUNT(*) > 1)
BEGIN;
ROLLBACK TRANSACTION;
RAISERROR('Duplicate Values Entered.', 16, 1);
RETURN;
END;
Or, as an alternative, use an indexed view:
CREATE VIEW dbo.MyView WITH SCHEMABINDING
AS
SELECT id -- more columns may be added to help other queries
FROM dbo.<BaseTable>
WHERE type = 'xyz';
go
CREATE UNIQUE CLUSTERED INDEX UQ_MyView ON MyView(id);
go
(Untested)
Hugo Kornelis, SQL Server MVP