Newbie need help on trigger.

Hello,
I need to write a trigger that removes repeated elements on a table.
I want to delete every row where the field "name" has beenrepeated.
Any hel in writing such a trigger would be so much higly appreciated.
Many thinks in advance
JBWhy do this via a trigger? Wouldn't it be easier to not insert duplicate
rows to begin with?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly appreciated.
> Many thinks in advance
> JB
>|||While this is a very good question, it appears that we
need the other option.
We are doing something very unusual. This is a system
integration project and we are sharing this database with other systems.
I won''t go in furthere details and hope that make sens.
Many thanks
JB|||You could try an INSTEAD OF trigger:
CREATE TRIGGER TG_NoDupes
ON YourTable
FOR INSERT
AS
BEGIN
IF @.@.ROWCOUNT = 0
RETURN
INSERT YourTable
SELECT *
FROM INSERTED
WHERE NOT EXISTS
(SELECT *
FROM YourTable
WHERE YourTable.Name = INSERTED.Name)
END
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:%23SifbHQHFHA.1176@.TK2MSFTNGP12.phx.gbl...
> While this is a very good question, it appears that we
> need the other option.
> We are doing something very unusual. This is a system
> integration project and we are sharing this database with other systems.
> I won''t go in furthere details and hope that make sens.
> Many thanks
> JB
>|||Jensen,
Unless I'm really not understanding, what you are asking for is inherently
inconsistent. Either you want a PROCEDURE that will delete all instances
where name has been repeated, or you want a TRIGGER to PREVENT the insert of
such records in the first place. An insert TRIGGER, for eg, will fire and
run EVERY TIME a record is inserted. If you wrote it to delete all existing
duplicates, it would be re-running that code over and over- again
unnecessarily, on every insert, when the first run would have already delete
d
all existing duplicates.
What it sounds like yuou might actually need, (excuse me if I've
mistunderastood) is a procedure to run ONCE to delete all existing
duplicates, and a trigger, (actually a unique constraint on the name Column
would do it) that would prevent furthur inserts of duplicates in the future.
Anyway, if so, for the first step, to eliminate existing dupes, sasuming the
table has a Primary Key, called say "PKID", try this:
Delete T
From TableName T
Where Name In (Select Name From TableName
Group By Name
Having Count(*) > 1)
And PKID <> (Select Min(PKID)
From TableName
Where Name = T.Name)
"Jensen bredal" wrote:

> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly appreciated.
> Many thinks in advance
> JB
>
>|||"Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> Hello,
> I need to write a trigger that removes repeated elements on a table.
> I want to delete every row where the field "name" has beenrepeated.
> Any hel in writing such a trigger would be so much higly
appreciated.
> Many thinks in advance
> JB
>
Jensen bredal,
May I ask why you are operating a table without a Primary Key? (Yes,
I did read the other portion of the thread where the "We're doing
something unusual" answer was given, but I'm still curious).
From the description, it sounds like you want to keep out duplicate
records. This is the effect a Primary Key has, and it's a lot faster
than any trigger.
Sincerely,
Chris O.|||Well as i said this is not the every day scenario.
I may give you a full explaination if you have time to read my answer. Let
me know if you want that.
JB
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:MPednYwUXbNt_r_fRVn-sA@.comcast.com...
> "Jensen bredal" <jensen.bredahl@.yahoo.com> wrote in message
> news:O9xhu5PHFHA.3624@.tk2msftngp13.phx.gbl...
> appreciated.
> Jensen bredal,
> May I ask why you are operating a table without a Primary Key? (Yes,
> I did read the other portion of the thread where the "We're doing
> something unusual" answer was given, but I'm still curious).
> From the description, it sounds like you want to keep out duplicate
> records. This is the effect a Primary Key has, and it's a lot faster
> than any trigger.
>
> Sincerely,
> Chris O.
>

Newb query on query :)

Hi,
Newb question here,
I have two tables (A & B) that I want to join and exclude the elements that
match, what would be the best aproach for this? thanks in advance. (BTW I'm
comparing 5 fields that have to match from each table: A1 = B1 And A2 = B2
... and so forth.)SELECT <columnList>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.Col1 = b.Col1 And A.Col2 = B.Col2 AND ...
WHERE A.Col1 IS NULL OR B.Col1 IS NULL
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Manny" <Manny@.discussions.microsoft.com> wrote in message
news:DA005CA3-B3FA-44C8-9F86-00CA28ACEFF2@.microsoft.com...
> Hi,
> Newb question here,
> I have two tables (A & B) that I want to join and exclude the elements
> that
> match, what would be the best aproach for this? thanks in advance. (BTW
> I'm
> comparing 5 fields that have to match from each table: A1 = B1 And A2 = B2
> ... and so forth.)
>|||Manny
> I have two tables (A & B) that I want to join and exclude the elements
that
> match,
You meant that all data from A that does not exist in B?
Look up LEFT/RIGHT/FULL join combinations in the BOL and WHERE NOT EXISTS
command as well.
"Manny" <Manny@.discussions.microsoft.com> wrote in message
news:DA005CA3-B3FA-44C8-9F86-00CA28ACEFF2@.microsoft.com...
> Hi,
> Newb question here,
> I have two tables (A & B) that I want to join and exclude the elements
that
> match, what would be the best aproach for this? thanks in advance. (BTW
I'm
> comparing 5 fields that have to match from each table: A1 = B1 And A2 = B2
> ... and so forth.)
>|||"Uri Dimant" wrote:
> You meant that all data from A that does not exist in B?
> Look up LEFT/RIGHT/FULL join combinations in the BOL and WHERE NOT EXISTS
> command as well.
Thanks Uri & Roji for your responses,
Yes, well I actually need 3 results:
1) Information that matches A & B (which was the easiest for me :)
2) Information that exists in A different from B (excluding the match)
3) Information that exists in B different from A (excluding the match)
I'll look up the join conditions you guys are sugesting, thanks again.