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.

Inside the trigger do something like:

IF EXISTS (SELECT * FROM Table T INNER JOIN INSERTED I ON T.Id = I.Id AND T.Type = I.Type)
RAISERROR('Upps',15,10)

If you do not want to use a trigger you can also use a native UNIQUE constraint which will keep track of the UNIQUE-ness.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your reply. Uniqueness would not work since if the type is not xyz it can be duplicated. I had to change my strategy a little bit. Why wouldn’t the following code work?

This is part of my trigger on table T1. I am trying to check if the records inserted to T1 is available in myDB.dbo.myTable or not (destination table). If it is available rollback T1. It does not do that although I insert the same records twice.

-- duplicate record check

SET @.step = 'Duplicate record'

IF EXISTS (

SELECT i.myID, i.Type

FROM INSERTED i INNER JOIN

myDB.dbo.myTable c ON i.myID = c.myID

GROUP BY i.myID, i.Type

HAVING (COUNT(*) > 1) AND (i.Type = 'In')

)

BEGIN

ROLLBACK transaction

RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)

Return

END

What is problem?

|||First of all the trigger will be only rolled back if already two instances of the record exists in the database and you want to insert a third one(I guess sthat this is not what you want right).

-- duplicate record check

SET @.step = 'Duplicate record'

IF EXISTS (

SELECT *

FROM INSERTED i INNER JOIN

myDB.dbo.myTable c ON i.myID = c.myID --You don′t need to use the three part name until the table is located on another database

WHERE i.Type = 'In' --Don′t you want to check if the existing data in the table is also of type c.type = 'In' ?

)

BEGIN

ROLLBACK transaction

RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)

Return

END

Do you really want to check another database for the value ? Or did you just do this for full declaration of the database reference ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks you very much for your help. Here is a little bit more detail. The trigger is on T1. T1 is inserted records by an external system. T1.Type can be “In’, or ‘NotIn’ If it is In I need to insert this to a remote database too which is myDB.dbo.myTable. There is no myTable.Type field in remote database.

1. So if T1.myID=1 and T1.Type=’In’ comes more than one and myID=1 is already available in remote database return error to the external system and do not accept insert into T1.

2. If T1.myID=1 and T1.Type=’NotIn’ can come more than one continue without error.

How should I do this?

|||

We are getting closer :-)

IF EXISTS
(
SELECT * FROM T1
INNER JOIN INSERTED I
ON T1.myId = I.MyId
AND T1.Type = I.Type
WHERE T1.Type = 'In'
INNER JOIN mydb.dbo.MyTable MT
ON MT.MyId = T.MyId
)
RAISE THE ERROR

In my word: The query sorts out, if already a myId record with the Type = 'In' exists (which is currently trying to insert into the table) and check if that is the case if there is another myId in the remote database. (Thats because of your "and" in "...than one AND myId=1...").

If there is already an entry in the local table but no in the remote table there won′t be an error presented (as of your explanation) If this is not the right behaviour you either explained it in a wrong way or I did understand it the wrong way :-)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks Jens. I will try this.