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.
>