Duplicate record trigger

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?JIM. H. wrote:
> This is part of my trigger on table T1. I am trying to check if the record
s
> 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?
Why not just add a unique constraint to the table instead?
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Jim,
It seems like if your goal is to prohibit the addition of rows having
duplicate values for columns MyID and [Type], then a UNIQUE constraint w
ould
have less performance impact than a trigger.
Preventing the duplicate row from being added to the table is far better
than trying to determine if a duplicate row HAS been added, and then
removing it after the fact.
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:876403CD-C4A3-4FB9-B278-CD792B39D1B2@.microsoft.com...
> 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?
>|||Hi Jim
I asked in your previous thread that you posted example DDL and data along
with statements you expected to work and those that you don't want to work.
Without doing so you have got the same replies as before a UNIQUE constraint
would be the best solution. For example:
CREATE DATABASE TESTDB
GO
USE TESTDB
GO
CREATE TABLE myTable ( MyId int NOT NULL,
myType char(2) NOT NULL,
CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'In' )
INSERT INTO myTable ( MyId, myType )
VALUES( 2, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
duplicate key in object 'dbo.myTable'.
The statement has been terminated.
*/
/* If this the above statement should have been allowed then you can not use
a unique constraint, but can make the exception in the trigger */
ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
GO
CREATE TRIGGER trg_myTable ON myTable
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT i.myID, i.myType
FROM INSERTED i
JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myType
= 'In'
GROUP BY i.myID, i.myType
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
Return
END
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'In' )
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
/* But this still works */
INSERT INTO myTable ( MyId, myType )
VALUES( 1, 'Ot' )
UPDATE myTable
SET myType = 'In'
WHERE MyId = 2
AND myType = 'Ot'
/*
Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
Error: step: Duplicate record. rollback is done.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
*/
John
"JIM.H." wrote:

> This is part of my trigger on table T1. I am trying to check if the record
s
> 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?
>|||Hi John,
Thank 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 da
tabase
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 an
d
do not accept insert into T1.
2. If T1.myID=1 and T1.Type=’NotIn’ can come more than one continue with
out
error.
How should I do this?
So in the example you gave
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'In' )
This should not let another (1,’In’) inserted.
INSERT INTO T1 ( MyId, Type )
VALUES( 1, 'Ot' )
This can be many times, there should not be any restriction on this, because
of this I could not use unique constraints on T1.
It seem your trg_myTable example is the same as my example (am I missing
anything) but it does not seem that is working, I am able insert (1,’In’
)
more than one.
Any idea would be greatly appreciated.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> I asked in your previous thread that you posted example DDL and data along
> with statements you expected to work and those that you don't want to work
.
> Without doing so you have got the same replies as before a UNIQUE constrai
nt
> would be the best solution. For example:
> CREATE DATABASE TESTDB
> GO
> USE TESTDB
> GO
>
> CREATE TABLE myTable ( MyId int NOT NULL,
> myType char(2) NOT NULL,
> CONSTRAINT UQ_myTable_MyId_myType UNIQUE CLUSTERED ( myId, myType ) )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'In' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 2, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> /*
> Msg 2627, Level 14, State 1, Line 1
> Violation of UNIQUE KEY constraint 'UQ_myTable_MyId_myType'. Cannot insert
> duplicate key in object 'dbo.myTable'.
> The statement has been terminated.
> */
> /* If this the above statement should have been allowed then you can not u
se
> a unique constraint, but can make the exception in the trigger */
> ALTER TABLE myTable DROP CONSTRAINT UQ_myTable_MyId_myType
> GO
> CREATE TRIGGER trg_myTable ON myTable
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT i.myID, i.myType
> FROM INSERTED i
> JOIN dbo.myTable c ON i.myID = c.myID AND i.myType = c.myType AND i.myTy
pe
> = 'In'
> GROUP BY i.myID, i.myType
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> Return
> END
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'In' )
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
> /* But this still works */
> INSERT INTO myTable ( MyId, myType )
> VALUES( 1, 'Ot' )
> UPDATE myTable
> SET myType = 'In'
> WHERE MyId = 2
> AND myType = 'Ot'
> /*
> Msg 50000, Level 16, State 1, Procedure trg_myTable, Line 17
> Error: step: Duplicate record. rollback is done.
> Msg 3609, Level 16, State 1, Line 1
> The transaction ended in the trigger. The batch has been aborted.
> */
>
> John
> "JIM.H." wrote:
>|||Hi Jim
The trigger is not the same as your code. As T1 it would be
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[
Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
RETURN
END
This would allow duplicates for any value of MyId and a Type of 'NotIn', but
only one occurance of a value of Type of 'In' with any single value of MyId.
If the table in your remote database can only be populated by this procedure
all you need to do is
insert the values into that database through the trigger
CREATE TRIGGER trg_T1 ON T1
FOR INSERT,UPDATE
AS
BEGIN
DECLARE @.step varchar(30)
-- duplicate record check
SET @.step = 'Duplicate record'
IF EXISTS (
SELECT *
FROM inserted i
JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.[
Type] = 'In'
GROUP BY i.myID, i.[Type]
HAVING COUNT(*) > 1
)
BEGIN
ROLLBACK transaction
RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
END
ELSE
BEGIN
INSERT INTO myDB.dbo.myTable ( myId )
SELECT MyId
FROM inserted i
LEFT JOIN myDB.dbo.myTable c ON i.myID =
c.myID
WHERE c.MyId IS NULL
AND i.[Type] = 'In'
END
RETURN
END
If records can be inserted into the remote database independent of this
process then would not be sufficient.
John
"JIM.H." wrote:
[vbcol=seagreen]
> Hi John,
> Thank 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 ca
n
> 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 wi
thout
> error.
> How should I do this?
> So in the example you gave
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'In' )
> This should not let another (1,’In’) inserted.
> INSERT INTO T1 ( MyId, Type )
> VALUES( 1, 'Ot' )
> This can be many times, there should not be any restriction on this, becau
se
> of this I could not use unique constraints on T1.
> It seem your trg_myTable example is the same as my example (am I missing
> anything) but it does not seem that is working, I am able insert (1,’In
)
> more than one.
> Any idea would be greatly appreciated.
>
> "John Bell" wrote:
>|||Thanks John. I will try to implement this.
"John Bell" wrote:
[vbcol=seagreen]
> Hi Jim
> The trigger is not the same as your code. As T1 it would be
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.&
#91;Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> RETURN
> END
> This would allow duplicates for any value of MyId and a Type of 'NotIn', b
ut
> only one occurance of a value of Type of 'In' with any single value of MyI
d.
> If the table in your remote database can only be populated by this procedu
re
> all you need to do is
> insert the values into that database through the trigger
> CREATE TRIGGER trg_T1 ON T1
> FOR INSERT,UPDATE
> AS
> BEGIN
> DECLARE @.step varchar(30)
> -- duplicate record check
> SET @.step = 'Duplicate record'
> IF EXISTS (
> SELECT *
> FROM inserted i
> JOIN dbo.T1 c ON i.myID = c.myID AND i.[Type] = c.[Type] AND i.&
#91;Type] = 'In'
> GROUP BY i.myID, i.[Type]
> HAVING COUNT(*) > 1
> )
> BEGIN
> ROLLBACK transaction
> RAISERROR('Error: step: %s. rollback is done.', 16, 1, @.step)
> END
> ELSE
> BEGIN
> INSERT INTO myDB.dbo.myTable ( myId )
> SELECT MyId
> FROM inserted i
> LEFT JOIN myDB.dbo.myTable c ON i.myID =
> c.myID
> WHERE c.MyId IS NULL
> AND i.[Type] = 'In'
> END
> RETURN
> END
> If records can be inserted into the remote database independent of this
> process then would not be sufficient.
> John
>
> "JIM.H." wrote:
>