August 13, 2014 at 1:23 am
HI There
I want to pick someones brain
I am using a Trigger to search for existing values in a column in a table to look if the value is already in the table before inserting:
here is my statement to look for duplications:
-- If a duplicate has been found, rollback trasaction and report error.
if EXISTS(select 1
FROM inserted
INNER JOIN Table1 M ON
inserted.ID = M.ID
WHERE inserted.Client_Id >= inserted.Client_Id)
Please can anyone tell me if this is correct as i been struggling for three days now and just cant get it to look for the duplications in the table that already exist:unsure:
August 13, 2014 at 1:36 am
julie.breetzke (8/13/2014)
HI ThereI want to pick someones brain
I am using a Trigger to search for existing values in a column in a table to look if the value is already in the table before inserting:
here is my statement to look for duplications:
-- If a duplicate has been found, rollback trasaction and report error.
if EXISTS(select 1
FROM inserted
INNER JOIN Table1 M ON
inserted.ID = M.ID
WHERE inserted.Client_Id >= inserted.Client_Id)
Please can anyone tell me if this is correct as i been struggling for three days now and just cant get it to look for the duplications in the table that already exist:unsure:
I would probably use another approach such as a unique constraint and a try/catch / except / not in / for the insert. Most certainly not a transaction manipulation in a trigger, makes things more complicated than necessary.
😎
August 13, 2014 at 1:41 am
i cannot use a constraint of sorts as it changes the structure of the database.
Can you possibly give me an example
August 13, 2014 at 2:11 am
Here is an example using IN / NOT IN for error report and insert. Same can be done with EXISTS / EXCEPT
😎
USE tempdb;
GO
DECLARE @CLIENT TABLE
(
CLIENT_ID INT NOT NULL
);
DECLARE @INCOMING TABLE
(
INCOMING_CLIENT_ID INT NOT NULL
);
INSERT INTO @CLIENT (CLIENT_ID)
VALUES (1),(2),(3),(4),(5),(6),(7),(8);
INSERT INTO @INCOMING(INCOMING_CLIENT_ID)
VALUES (8),(9),(10);
/* ERROR REPORT */
SELECT
INCOMING_CLIENT_ID
FROM @INCOMING I
WHERE I.INCOMING_CLIENT_ID IN
(SELECT CLIENT_ID FROM @CLIENT);
/* INSERT WITHOUT DUPLICATION */
INSERT INTO @CLIENT(CLIENT_ID)
SELECT
INCOMING_CLIENT_ID
FROM @INCOMING I
WHERE I.INCOMING_CLIENT_ID NOT IN
(SELECT CLIENT_ID FROM @CLIENT);
/* VERIFY THE INSERT */
SELECT
C.CLIENT_ID
FROM @CLIENT C;
Error Report Result
INCOMING_CLIENT_ID
------------------
8
Result after insert
CLIENT_ID
-----------
1
2
3
4
5
6
7
8
9
10
August 13, 2014 at 2:22 am
IN MY CODE, something like this:
if EXISTS(SELECT AlphanumericCol FROM AMGR_User_Fields_Tbl I
WHERE I.AlphanumericCol IN(SELECT CLIENT_ID FROM AMGR_User_Fields_Tbl)
August 13, 2014 at 2:26 am
apologies for my ignorance however im new at this..
Must i not use a trigger or must i just use a different trigger?
How would i incorporate the code above into my trigger?
August 13, 2014 at 7:54 am
julie.breetzke (8/13/2014)
apologies for my ignorance however im new at this..Must i not use a trigger or must i just use a different trigger?
How would i incorporate the code above into my trigger?
What folks are suggesting is that you would be better served by having whatever process needs to perform the inserts, do the checking, instead of using a trigger for that purpose, given that you are unable to effect a change to the db structure. Unless you have an un-modifiable application that does them, a trigger isn't necessarily the best option.
Your requirement for not having duplicates is clear, but a little more in the way of specifics might make it a lot easier to write that trigger. What data type is the value that needs to be checked for duplication? Is there an index on that field in the existing table? Performance could become a nightmare without an index, which is why folks don't like the idea of the trigger very much. Does the definition of duplicate rely on one and only one field? As you indicate you're struggling, can you show us with some sample code what is vs. what isn't happening with what you have in place right now?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply