Help get this topic noticed by sharing it on Twitter, Facebook, or email.

@@Identity Commands to be changed to SCOPE_IDENTITY()

Hi there, I have been having problems which I have found out to be caused by automatic created identity select in Code on Time, when there is a trigger in a table that touches other records the @@Identity Command will be returning the value of last record touched, this causes problem with one to many, many to many fields as well as with displaying the edit form after inserting.

I reckon that if code on time change the select @@identity to select SCOPE_IDENTITY() for the automatic generated code it will avoid a lot of problems with saving related records to the wrong master record and also will consistently save the child records to the correct master.

This is a suggestion that would greatly improve my life because at the moment I am having to edit the commands manually so it returns the right ID, but I also don't like customising commands as I rather have that generated automatically by Code on time.

I hope you can take my suggestion on board and improve code on time with it.
1 person likes
this idea
+1
Reply
  • Vivi:

    Then something like this:


    NodeSet().Select("//command[@event='Inserted']/text]").Value("select SCOPE_IDENTITY()");


    in your VirtualizeContoller method might be useful.

    Best regards,

    Roberto
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly sad, anxious, confused, frustrated

  • Hi Roberto, I have been able to edit the command in designer mode, check the box as custom code and put the select as I have described.

    My point is that it would be nice that code on time generated this as the identity command automatically as the scope_identity will always return the ID that we expect while the @@Identity will return the id of the last table touched which when you have a trigger touching other record is likely to be different.

    At the moment I have a work around already, I have just sent this as an idea so they can evaluate the idea and hopefully implement to save us time customising things that could come from code on time automatically and deliver a better result than what is in place. If that makes sense.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. happy, confident, thankful, excited indifferent, undecided, unconcerned kidding, amused, unsure, silly sad, anxious, confused, frustrated