Query

I have duplicate records in a table based on say
OrderNum, I would like to copy 1st record for each
OrderNum from this table into a new table and then update
the OrderNum record in the 2nd table with values from
rest of the records in the 1st table, can someone help me
in doing this?
Ex.
Frist table may have -
ORder1 AreaField1 description ...
ORder1 AreaField1 Description ...
Order1 AreaField2 Description ...
ORder2 AreadField Description ...
Order2 AreaField Description ...
ORder3 AreaField Description ...
ORder4 AreaField Description ...
I would like the 2nd table to look lik -
ORder1 AreaField1+AreaField2 Description ...
ORder2 AreaField Description ...
Order3 AreaField Description ...
ORder4 AreaField Description...
Thank you very much,
-Kim
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.mseq:7748
On Thu, 12 Aug 2004 11:56:32 -0700, Kim wrote:
(snip)
Hi Kim,
I just replied to an equal post in .programming.
Please don't multi-post!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

query

hi
i have a doubt in sqlserver.
how to delete the duplicate rows with a query in sqlserver
thank youOne choice is to use Cursor.
But remember that Cursors are very expensive to SQL Server.

Else,

You may try to use the UNION (not UNION ALL), with the same table. Put all the returned data into a temporary Table, then you delete your data from original table and insert the values from the temporary table into it.|||First of all, what is structure of your table?
If your table does not have a primary key - too bad... Use Diogo's advice and then check a normalization rules.
If it does - you can try next method:

drop table test
go
create table test (id int primary key,
code varchar(10))
go
insert test values(1,'code1')
insert test values(2,'code2')
insert test values(3,'code1')
insert test values(4,'code3')
insert test values(5,'code4')
insert test values(6,'code5')
insert test values(7,'code6')
insert test values(8,'code3')

select *
--delete
from test
where id in
(select min(Id) from test group by code having count(*)>1)

May be it needs to run last query couple times....

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 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.
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-handling-I.html#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 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.
>
|||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 the
> 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 sample
code.
John
"JIM.H." wrote:
[vbcol=seagreen]
> 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:
|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"Hugo Kornelis" wrote:

> On Thu, 23 Nov 2006 16:27:30 -0800, Arnie Rowland wrote:
>
> 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
>
|||Or doens't like the responses.
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
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...[vbcol=seagreen]
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "Hugo Kornelis" wrote:

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

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 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.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-handling-I.html#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 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.
>|||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-handling-I.html#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
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#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
>>Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
>>Adapted from http://www.sommarskog.se/error-handling-I.html#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|||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 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
> >>
> >>Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> >>Adapted from http://www.sommarskog.se/error-handling-I.html#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
>
>|||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
> >
> >Why not have a unique index on these two columns?
> Hi John,
> Agreed 100%.
>
> >Adapted from http://www.sommarskog.se/error-handling-I.html#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
>|||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 sample
code.
John
"JIM.H." wrote:
> 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
> > >
> > >Why not have a unique index on these two columns?
> >
> > Hi John,
> >
> > Agreed 100%.
> >
> >
> > >Adapted from http://www.sommarskog.se/error-handling-I.html#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
> >|||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|||Hi Hugo
Jim seems to start a new thread each time he hits a problem with this trigger!
John
"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
>|||Or doens't like the responses.
--
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
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:C96ED8FF-B564-4960-93DD-2F139ADCB18E@.microsoft.com...
> Hi Hugo
> Jim seems to start a new thread each time he hits a problem with this
> trigger!
> John
>
> "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