|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
For insert trigger problemAfter inserting any new record in the table and jump to the next one, a totally different record appears instead of the one I just inserted. After that, when I retrieved all the records again I can find my record their stored without any problems. The only thing I have special with this table is a for insert trigger to track 2 fields on a different table. I figure out that problem is happening because of this trigger. The trigger is like the following:- CREATE TRIGGER track ON dbo.Contacts FOR Insert AS insert tblResult (ID, NewValue) select inserted.contactID,inserted.ContactDetails from inserted So is there is anyone faced this problem before or have any explanation of how I can re-write this trigger to avoid causing that problem? Thanks, Nader I'm not sure what you mean by "jump to the next one", but if you mean you
are using @@Identity to get the identity value of the row you just entered, you want to use Scope_Identity() instead. Look up Scope_Identity in BOL. If you mean something else, could you explain a little more? Tom Show quoteHide quote "Nader Shahin" <NaderSha***@discussions.microsoft.com> wrote in message news:740B77FF-DE36-4AF5-B324-AD7E8380E69D@microsoft.com... >I have something weird happening with one of my tables. > After inserting any new record in the table and jump to the next one, a > totally different record appears instead of the one I just inserted. After > that, when I retrieved all the records again I can find my record their > stored without any problems. > > The only thing I have special with this table is a for insert trigger to > track 2 fields on a different table. I figure out that problem is > happening > because of this trigger. > > The trigger is like the following:- > > CREATE TRIGGER track ON dbo.Contacts > FOR Insert > AS > > insert tblResult (ID, NewValue) > select inserted.contactID,inserted.ContactDetails > from inserted > > So is there is anyone faced this problem before or have any explanation of > how I can re-write this trigger to avoid causing that problem? > > Thanks, > Nader That was a good call Tom.
Actually because I had another ID column on the tracking table with Identity Constraints, after I inserted any new record on the Main table it showed a record matched the identity on the tracking table not the one on the main table. I solved the problem by dropping the Identity constrains from the tracking table and the trigger working fine now. Thanks. Nader Shahin Show quoteHide quote "Tom Cooper" wrote: > I'm not sure what you mean by "jump to the next one", but if you mean you > are using @@Identity to get the identity value of the row you just entered, > you want to use Scope_Identity() instead. Look up Scope_Identity in BOL. > If you mean something else, could you explain a little more? > > Tom > > "Nader Shahin" <NaderSha***@discussions.microsoft.com> wrote in message > news:740B77FF-DE36-4AF5-B324-AD7E8380E69D@microsoft.com... > >I have something weird happening with one of my tables. > > After inserting any new record in the table and jump to the next one, a > > totally different record appears instead of the one I just inserted. After > > that, when I retrieved all the records again I can find my record their > > stored without any problems. > > > > The only thing I have special with this table is a for insert trigger to > > track 2 fields on a different table. I figure out that problem is > > happening > > because of this trigger. > > > > The trigger is like the following:- > > > > CREATE TRIGGER track ON dbo.Contacts > > FOR Insert > > AS > > > > insert tblResult (ID, NewValue) > > select inserted.contactID,inserted.ContactDetails > > from inserted > > > > So is there is anyone faced this problem before or have any explanation of > > how I can re-write this trigger to avoid causing that problem? > > > > Thanks, > > Nader > > > That was a good call Tom.
Actually because I had another ID column on the tracking table with Identity Constraints, after I inserted any new record on the Main table it showed a record matched the identity on the tracking table not the one on the main table. I solved the problem by dropping the Identity constrains from the tracking table and the trigger working fine now. Thanks. Nader Shahin Show quoteHide quote "Tom Cooper" wrote: > I'm not sure what you mean by "jump to the next one", but if you mean you > are using @@Identity to get the identity value of the row you just entered, > you want to use Scope_Identity() instead. Look up Scope_Identity in BOL. > If you mean something else, could you explain a little more? > > Tom > > "Nader Shahin" <NaderSha***@discussions.microsoft.com> wrote in message > news:740B77FF-DE36-4AF5-B324-AD7E8380E69D@microsoft.com... > >I have something weird happening with one of my tables. > > After inserting any new record in the table and jump to the next one, a > > totally different record appears instead of the one I just inserted. After > > that, when I retrieved all the records again I can find my record their > > stored without any problems. > > > > The only thing I have special with this table is a for insert trigger to > > track 2 fields on a different table. I figure out that problem is > > happening > > because of this trigger. > > > > The trigger is like the following:- > > > > CREATE TRIGGER track ON dbo.Contacts > > FOR Insert > > AS > > > > insert tblResult (ID, NewValue) > > select inserted.contactID,inserted.ContactDetails > > from inserted > > > > So is there is anyone faced this problem before or have any explanation of > > how I can re-write this trigger to avoid causing that problem? > > > > Thanks, > > Nader > > > I'm glad it's working now. And, since you don't need the Identity column in
the tracking table, removing it is certainly a good workaround to get your system working. But I would recommend two additional things. First, in future code, use Scope_Identity() rather than @@Ientity and second, make a plan to clean up any current uses of @@Identity. As you have seen, in most cases, @@Identity is just a potential bug waiting to happen. Tom Show quoteHide quote "Nader Shahin" <NaderSha***@discussions.microsoft.com> wrote in message news:34D43182-7A09-4FEE-AF9B-C24E48C5F184@microsoft.com... > That was a good call Tom. > > Actually because I had another ID column on the tracking table with > Identity > Constraints, after I inserted any new record on the Main table it showed a > record matched the identity on the tracking table not the one on the main > table. > > I solved the problem by dropping the Identity constrains from the tracking > table and the trigger working fine now. > > Thanks. > > Nader Shahin > > > "Tom Cooper" wrote: > >> I'm not sure what you mean by "jump to the next one", but if you mean you >> are using @@Identity to get the identity value of the row you just >> entered, >> you want to use Scope_Identity() instead. Look up Scope_Identity in BOL. >> If you mean something else, could you explain a little more? >> >> Tom >> >> "Nader Shahin" <NaderSha***@discussions.microsoft.com> wrote in message >> news:740B77FF-DE36-4AF5-B324-AD7E8380E69D@microsoft.com... >> >I have something weird happening with one of my tables. >> > After inserting any new record in the table and jump to the next one, a >> > totally different record appears instead of the one I just inserted. >> > After >> > that, when I retrieved all the records again I can find my record their >> > stored without any problems. >> > >> > The only thing I have special with this table is a for insert trigger >> > to >> > track 2 fields on a different table. I figure out that problem is >> > happening >> > because of this trigger. >> > >> > The trigger is like the following:- >> > >> > CREATE TRIGGER track ON dbo.Contacts >> > FOR Insert >> > AS >> > >> > insert tblResult (ID, NewValue) >> > select inserted.contactID,inserted.ContactDetails >> > from inserted >> > >> > So is there is anyone faced this problem before or have any explanation >> > of >> > how I can re-write this trigger to avoid causing that problem? >> > >> > Thanks, >> > Nader >> >> >>  
Other interesting topics
Little bug in sql2005?
Need Consecutive Query problem with synonyms SQL search for similar records with different dates Different result from Script and SP Set Database Context inside a stored proc Explain this please! Passing tableName as parameter to SP Table Design Sequentially Numbering Groups of Records |
|||||||||||||||||||||||
