Hello,
I would like to prevent Contacts being inserted into the database when inserted Contact:
firstname + ', ' + lastname + ', ' + workphone
is identical with existing Contacts:
firstname + ', ' + lastname + ', ' + workphone
Can this be done with an Insert Trigger and if so, how? Thank you in advance,
LarsYou could add an alternate key to the entity on those attributes. In your physical implementation, add a "UNIQUE" constraint and a unique index on those columns. It could be done with a trigger, but it will probably not preform as well.|||I'll second that recommendation. A UNIQUE constraint should be more efficient (and simpler) than any trigger you could write.
-PatPsql
Showing posts with label inserted. Show all posts
Showing posts with label inserted. Show all posts
Tuesday, March 27, 2012
Duplicate records are being inserted with one insert command.
This is like the bug from hell. It is kind of hard to explain, so
please bear with me.
please bear with me.
Background Info: SQL Server 7.0, on an NT box, Active Server pages
with Javascript, using ADO objects.
I'm inserting simple records into a table. But one insert command is
placing 2 or 3 records into the table. The 'extra' records, have the
same data as the previous insert incident, (except for the timestamp).
Here is an example. Follow the values of the 'Search String' field:
I inserted one record at a time, in the following order (And only one
insert per item):
airplane
jet
dog
cat
mouse
tiger
After this, I should have had 6 records in the table. But, I ended
up with 11!
Here is what was recorded in the database:
Vid DateTime Type ProductName SearchString NumResults
cgcgGeorgeWeb3 Fri Sep 26 09:48:26 PDT 2003 i null airplane 112
cgcgGeorgeWeb3 Fri Sep 26 09:49:37 PDT 2003 i null jet 52
cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null dog 49
cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52
cgcgGeorgeWeb3 Fri Sep 26 09:50:00 PDT 2003 i null jet 52
cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null dog 49
cgcgGeorgeWeb3 Fri Sep 26 09:50:22 PDT 2003 i null cat 75
cgcgGeorgeWeb3 Fri Sep 26 09:52:53 PDT 2003 i null mouse 64
cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null tiger 14
cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64
cgcgGeorgeWeb3 Fri Sep 26 09:53:06 PDT 2003 i null mouse 64
Look at the timestamps, and notice which ones are the same.
I did one insert for 'dog' , but notice how 2 'jet' records were
inserted
at the same time. Then, when I inserted the 'cat' record, another
'dog' record was inserted. I waited awhile, and inserted mouse, and
only the mouse was inserted. But soon after, I inserted 'tiger', and 2
more mouse records were inserted.
If I wait awhile between inserts, then no extra records are inserted.
( Notice 'airplane', and the first 'mouse' entries. ) But if I insert
records right after one another, then the second record insertion also
inserts a record with data from the 1st insertion.
Here is the complete function, in Javascript (The main code of
interest
may start at the Query = "INSERT ... statement):
----------------------
//Write SearchTrack Record -----------
Search.prototype.writeSearchTrackRec = function(){
Response.Write ("<br>Calling function writeSearchTrack \n"); // for
debug
var Query;
var vid;
var type = "i"; // Type is image
var Q = "', '";
var datetime = "GETDATE()";
//Get the Vid
// First - try to get from the outVid var of Cookieinc
try{
vid = outVid;
}catch(e){
vid = Request.Cookies("CGIVid"); // Gets cookie id value
vid = ""+vid;
if (vid == 'undefined' || vid == ""){
vid = "ImageSearchNoVid";
}
}
try{
Query = "INSERT SearchTrack (Vid, Type, SearchString, DateTime,
NumResults) ";
Query += "VALUES ('"+vid+Q+type+Q+this.searchString+"',
"+datetime+","+this.numResults+ ")";
this.cmd.CommandText = Query;
this.cmd.Execute();
}catch(e){
writeGenericErrLog("Insert SearchTrack failed", "Vid: "+vid+"
- SearchString:: "+this.searchString+" - NumResults: "+this.numResults
, e.description);
}
}//end
--------------------
I also wrote a non-object oriented function, and created the command
object inside the function. But I had the same results.
I know that the function is not getting called multiple times
because I print out a message each time it is called.
This really stumps me. I'll really appreciate any help you can
offer.
Thanks,
George"george" <georgem@.crystalgraphics.com> wrote in message
news:620c2f02.0309261014.20789ca0@.posting.google.c om...
> This is like the bug from hell. It is kind of hard to explain, so
> please bear with me.
<snip
I have no idea what's causing the issue, but the usual advice is to use
Profiler to trace the SQL which is getting sent to the database. You may see
something in the statements which helps you pin down what's going on.
Another thing to check is if there is an INSERT trigger on the table.
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<3f756b8c_4@.news.bluewin.ch>...
> "george" <georgem@.crystalgraphics.com> wrote in message
> news:620c2f02.0309261014.20789ca0@.posting.google.c om...
> > This is like the bug from hell. It is kind of hard to explain, so
> > please bear with me.
> <snip>
> I have no idea what's causing the issue, but the usual advice is to use
> Profiler to trace the SQL which is getting sent to the database. You may see
> something in the statements which helps you pin down what's going on.
> Another thing to check is if there is an INSERT trigger on the table.
> Simon
Thanks for your help. I found the cause. This is a web page that
contained a form. The form had a button with an onClick event handler
that evolked a script that submitted the form. Well, I added another
form to the page, and wanted the form to be submitted when the user
pushed the 'enter' key, so I focused the new submit button, and put an
onSubmit event handler in the new form. But the onSubmit event handler
evolked the before-mentioned script, so apparently two forms were
being submitted. One form had a hidden form element that contained
the previous 'search string' and the other form had an input box for
the current 'search string'. It explains why 2 db inserts happened
when I submitted the form, but it does not explain why sometimes 3
inserts happened. Anyway, it was kind of difficult to debug, because
it was not evident that 2 forms were being submitted, because, to the
user, the page worked just fine.
Well, perhaps this incident will help someone else down the road who
may run into this situation.
George
Monday, March 26, 2012
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 (
SELECTi.myID, i.Type
FROMINSERTED 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?
Moving to the T-SQL forum.|||
IF EXISTS (SELECT *
FROM inserted i
JOIN T1 T ON i.id = T1.id AND i.type = T1.type
)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Duplicate Values Entered.', 16, 1)
RETURN
ENDsql
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?
You may not use the SET statement and I also did not see an aggregate function with your GROUP BY which means it is not a GROUP BY but a standard DISTINCT. The link below covers what you need to know and a sample of what you can do in SQL Server. Hope this helps.
http://www.sommarskog.se/error-handling-I.html
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 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?
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 would
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 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 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 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?
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:
> 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 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
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:
> 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 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?
> 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:
> > 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 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?
> > >
> > >|||Thanks John. I will try to implement this.
"John Bell" 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:
> > 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 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?
> >
> > 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:
> >
> > > 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 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?
> > > >
> > > >
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 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?
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 would
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 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 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 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?
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:
> 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 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
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:
> 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 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?
> 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:
> > 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 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?
> > >
> > >|||Thanks John. I will try to implement this.
"John Bell" 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:
> > 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 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?
> >
> > 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:
> >
> > > 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 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?
> > > >
> > > >
Duplicate Inserted Record
Hi Everybody
This Code duplicate the record in the database, can somebody help me understand why that happen.
Thanks a LOT
CompanyName:
<asp:textbox id="txtCompanyName" runat="server" /><br />
Phone:
<asp:textbox id="txtPhone" runat="server" /><br />
<br />
<asp:button id="btnSubmit" runat="server" text="Submit" onclick="btnSubmit_Click" />
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"
insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@.CompanyName, @.Phone)"
selectcommand="SELECT * FROM [items]">
<insertparameters>
<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />
<asp:controlparameter controlid="txtPhone" name="Phone" />
</insertparameters>
</asp:sqldatasource>
VB
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlDataSource1.Insert()
End Sub
End Class
--------------
Yes is an Identity the Primary Key of the Table items
Please include the btnSubmit_Click ~ Code ~
Also, verify that the primary key in items is an identity
DK
|||You have the click event hooked up twice (remove one of the red sections):
CompanyName:
<asp:textbox id="txtCompanyName" runat="server" /><br />
Phone:
<asp:textbox id="txtPhone" runat="server" /><br />
<br />
<asp:button id="btnSubmit" runat="server" text="Submit"onclick="btnSubmit_Click" />
<asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:dsn %>"
insertcommand="INSERT INTO [items] ([smId], [iTitleSP]) VALUES (@.CompanyName, @.Phone)"
selectcommand="SELECT * FROM [items]">
<insertparameters>
<asp:controlparameter controlid="txtCompanyName" name="CompanyName" />
<asp:controlparameter controlid="txtPhone" name="Phone" />
</insertparameters>
</asp:sqldatasource>
VB
Partial Class Default2
Inherits System.Web.UI.Page
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs)Handles btnSubmit.Click
SqlDataSource1.Insert()
End Sub
End Class
Subscribe to:
Posts (Atom)