Is this Identity column a part of the key for your entity, or is it just there for database synchronization? If it is not in use in the action diagram, you could probably add "TBL Column FLD" triples to the Physical table for the entity for ALL columns on the table, and just not include that column on the entity itself. This allows you to model a physical table that has columns that you will not be using.
If this identity column is part of your key structure, you will need it to be present in the Update view, and you will probably want to add code to your Insert that determines what the identity used was, and returns it for any future update values. That makes things more difficult.
Original Message:
Sent: 07-20-2021 07:38 AM
From: Wayne Morgan
Subject: Update tables when Identity column doesnt allow Updates
To Add.. - I have attempted to use The 'Positioned Updates' Option in the INI - but his still generates the Identity Column in the Update Statement to be sent to the Database
I am able to create a separate view without the Identity column that works well for the updates, but I cannot use on the Update function - the only close way of doing this is to try and find a 'unique key' from the remaining columns and use that - however in most cases, the reason for using the 'Identity' column is because there isnt a unique key to be used with the remaining columns.
just looking for a better way than using hand created SQL statements
------------------------------
Wayne Morgan
Application Developer
SS&C Technologies
Original Message:
Sent: 07-19-2021 12:32 PM
From: Wayne Morgan
Subject: Update tables when Identity column doesnt allow Updates
using the standard plex 'update' action diagram option - on a table that has an Identity column - we get an error "Explicit value specified for identity field in table 'xxx' when 'SET IDENTITY_UPDATE is Off'
there is a 'Delete Where' but not an 'Update Where'
this is obviously set by the table/database level but as the update view includes the Identity column - (I remove from the Insert view and this works)
I can obviously add a Exec SQL to perform an Update Table 'Where identitycolumn = parameter - but then I have to add source for each table
I have tried dropping the identity column - but then the update doesnt happen -
can anyone figured a 'cleaner' way to do this when Updating rows with an Identity column ?
------------------------------
Wayne Morgan
Application Developer
SS&C Technologies
------------------------------