Re: What's wrong with this trigger

Ok, never mind about all that. I actually had SET NOCOUNT OFF at the top,
instead of SET NOCOUNT ON. *BLUSH* It is working now, without the identity
workaround, even though both the triggered table and the table underlying
the view have identities.

<mh> wrote in message news:OBcb98yTFHA.548@xxxxxxxxxxxxxxxxxxxxxxx
> Note also that my issue is not quite the same as I'm not getting the same
> error, but I think the underlying cause could be similar. My error is
> 'Key column information is insufficient or incorrect. Too many rows were
> affected by update.'
>
> <mh> wrote in message news:OaTiI4yTFHA.3140@xxxxxxxxxxxxxxxxxxxxxxx
>>I don't have that option in this case. The purpose of the downstream
>>tables is to update another application with changes that were made. The
>>table that has the trigger is control data, so the results that are moved
>>over to the changes table are not the records that are in the table, but
>>records that are affected by changing the table. I use an identity column
>>to have the records in sequential order of when they were created, so the
>>application that picks up the changes can get the latest change. I don't
>>understand why capturing the identity as Malcolm suggested, doesn't work.
>>The solution he had, had a typo, because there was no closing parenthesis,
>>but I fixed that and still have the same error. If it was up to me, I
>>would scrap the Access app at this point, but I don't have time for the
>>redesign as of yet. The ONLY reason I'm having this issue is because of
>>Access, and the app that uses this data is .NET. It's very frustrating,
>>especially since a legacy app is preventing me from moving forward.
>>
>> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
>> wrote in message news:OhEtFyyTFHA.2872@xxxxxxxxxxxxxxxxxxxxxxx
>>> Yes, insert triggers that change the @@identity value cause many trouble
>>> with ADP. See
>>> http://support.microsoft.com/default.aspx?scid=kb;EN-US;275090 for
>>> example.
>>>
>>> As suggested by Jeff in another posting, the easiest solution is to
>>> remove the identity value from the historical tables; for example by
>>> using the same values for the primary key as the ones used in the
>>> original tables. Another solution would be to use your own stored
>>> procedure to create the insert and then resynchronise the form.
>>>
>>> --
>>> Sylvain Lafontaine, ing.
>>> MVP - Technologies Virtual-PC
>>>
>>>
>>> <mh> wrote in message news:Os1vHcyTFHA.3952@xxxxxxxxxxxxxxxxxxxxxxx
>>>>I meant to say "SET NOCOUNT ON"...
>>>>
>>>> <mh> wrote in message news:%23C$KlZyTFHA.336@xxxxxxxxxxxxxxxxxxxxxxx
>>>>>I have a problem that I think might be related to this, where I have an
>>>>>update trigger that inserts an audit row into a view. It works fine
>>>>>from SQL Server Enterprise Manager, but when I go into the table
>>>>>through the ADP, it gives me the error 'Key column information is
>>>>>insufficient or incorrect. Too many rows were affected by update.' I
>>>>>tried the trick of SET NOCOUNT OFF and caching the identity, however I
>>>>>am still receiving the same error. Not sure what else to try! The
>>>>>primary key is in fact an identity and so is the view that I'm
>>>>>inserting the audit row into.
>>>>>
>>>>> Any help you can provide would be greatly appreciated!
>>>>> MH
>>>>>
>>>>> "Jeff via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxx> wrote in
>>>>> message news:468ab38d96a242479d01dd4ab0790c3a@xxxxxxxxxxxxxxxxxxxx
>>>>>> It only occurs when the tigger is enables. I can't seem to get the
>>>>>> EXECUTE
>>>>>> command to work though.
>>>>>>
>>>>>> EXECUTE (N'SELECT Identity (Int, ' + Cast(@myID As Varchar(10)) + ' ,
>>>>>> 1) AS
>>>>>> id
>>>>>> INTO #Tmp'
>>>>>>
>>>>>> What exactly does this do?
>>>>>>
>>>>>> --
>>>>>> Message posted via http://www.accessmonster.com
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>


.