|
|
The same problem occurs with other reference values from other reference tables linked to the table oc_tOccurenceRecord. For all referenced tables I can't use ON INSERT trigger. |
|
|
How do we reproduce this? Here is what we tried: create table oc_tOccurenceLocality (OccurenceLocalityID integer primary key); create table oc_tOccurenceRecord (OccurenceRecordID integer primary key, OccurenceLocalityID integer references oc_tOccurenceLocality(OccurenceLocalityID)); insert into oc_tOccurenceLocality values (1); insert into oc_tOccurenceLocality values (2); insert into oc_tOccurenceRecord values (1, 1); update oc_tOccurenceRecord set OccurenceLocalityID = 2 where OccurenceRecordID = 1; It worked as expected. sym_data.png (22,800 bytes) |
|
|
In the web-form (with table oc_tOccurenceRecord in background) is new value of Locality name filled (by user) "on the fly". It means that, when the locality name isn't listed in "autocomplete menu" of the form field "Locality", the user writes new Locality name. This new locality name is automatically inserted into the table "oc_tOccurenceLocality" in the background, with new (autoincremented) OccurenceLocalityID. Finally, user writes new OccurenceRecord and submits web-form. After this action I see in "sym_data"."row_data" all values (submitted from web-form), but value of OccurenceLocalityID is value of "data_id". 2020-11-19_22-34-57.png (6,836 bytes) |
|
|
I attach excel file with exported sym_data. First record (data_id = 2552199) relates with the activation "sync_on_insert=1" for table oc_tOccurenceLocality. Second record (data_id = 2552200) and third rercord (data_id = 2552201), both relate with updating new OccurenceRecord (in which is new OccurenceLocality created "on the fly") New locality (created "on the fly") is finally saved into the table with OccurenceLocalityID = 157527 at 19:56:04.950 Web form with OccurenceRecord (with OccureceRecordID = 3007557) is saved at 19:56:04.973 The OccurenceRecord is saved with OccurenceLocalityID= 2552200 instead of OccurenceLocalityID=157527. |
|
|
I found that problem with an changed referenced value (in the "sym_data"."row_data" of the main OccurenceRecord) to "sym_data"."data_id" value is only by the fields "OccurenceLocalityID", "OccurenceProjectID", which reference to "PRIMARY KEY" fields of the register tables: "oc_tOccurenceLocality" and "oc_tOccurenceProject". By these tables cannot by set "INSERT TRIGGER" (sync_on_insert=1), because it occurs the inserting of the "sym_data"."data_id" value of previous "sym_data" record, which points to "event_type=I" of referenced "table_name" ("oc_tOccurenceLocality", "oc_tOccurenceProject", ...). "sym_data" records are created by the trigger on the source table. Can this problem relates with the fact, that source database system is MSSQL ? I attach the definition of the generated INSERT trigger, which I can not have:
create trigger SYM_ON_I_FOR_CRP_TBND_NTRNTVYPRTL_TCCRNCLCLTY_CRP on "IntranetovyPortal"."dbo"."oc_tOccurenceLocality" with execute as caller after insert as
begin
declare @NCT int
set @NCT = @@OPTIONS & 512
set nocount on
declare @TransactionId varchar(1000)
if (@@TRANCOUNT > 0) begin
select @TransactionId = convert(VARCHAR(1000),transaction_id) from sys.dm_exec_requests where session_id=@@SPID and open_transaction_count > 0
end
if ("SM_testovacia".dbo.sym_triggers_disabled() = 0) begin
insert into "SM_testovacia"."dbo".sym_data
(table_name, event_type, trigger_hist_id, row_data, channel_id, transaction_id, source_node_id, external_data, create_time)
select 'oc_tOccurenceLocality','I', 211,
case when inserted."OccurenceLocalityID" is null then '' else ('"' + convert(varchar(40), inserted."OccurenceLocalityID",2) + '"') end+','+
case when inserted."OccurenceLocalityTitle" is null then '' else '"' + replace(replace(convert(nvarchar(255),inserted."OccurenceLocalityTitle") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."OccurenceLocalityDescription" is null then '' else '"' + replace(replace(convert(nvarchar(2000),inserted."OccurenceLocalityDescription") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."OccurenceLocalityType" is null then '' else '"' + replace(replace(convert(nvarchar(30),inserted."OccurenceLocalityType") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."OwnerID" is null then '' else ('"' + convert(varchar(40), inserted."OwnerID",2) + '"') end+','+
case when inserted."OccurenceLocalityCode" is null then '' else '"' + replace(replace(convert(nvarchar(30),inserted."OccurenceLocalityCode") ,'\','\\'),'"','\"') + '"' end+','+
case when inserted."Created" is null then '' else ('"' + convert(varchar,inserted."Created",121) + '"') end+','+
case when inserted."CreatorID" is null then '' else ('"' + convert(varchar(40), inserted."CreatorID",2) + '"') end+','+
case when inserted."LastUpdated" is null then '' else ('"' + convert(varchar,inserted."LastUpdated",121) + '"') end+','+
case when inserted."OperatorID" is null then '' else ('"' + convert(varchar(40), inserted."OperatorID",2) + '"') end+','+
case when inserted."Status" is null then '' else '"' + replace(replace(convert(nvarchar(30),inserted."Status") ,'\','\\'),'"','\"') + '"' end,
'oe_transaction', @TransactionId, "SM_testovacia".dbo.sym_node_disabled(), null, current_timestamp
from inserted
where 1=1
end
if (@NCT = 0) set nocount off
end
---- go
|
|
|
What about IDENTITY property ? The structure of problematic tables in the source SQL server instance are: CREATE TABLE oc_tOccurenceLocality (
OccurenceLocalityID int IDENTITY(1,1) NOT NULL,
OccurenceLocalityTitle nvarchar(255),
...
CONSTRAINT PK_oc_tOccurenceLocality PRIMARY KEY (OccurenceLocalityID)
)
CREATE TABLE IntranetovyPortal.dbo.oc_tOccurenceProject (
OccurenceProjectID int IDENTITY(1,1) NOT NULL,
OccurenceProjectTitle nvarchar(255),
...
CONSTRAINT PK_oc_tOccurenceProject PRIMARY KEY (OccurenceProjectID)
)Both of these Primary keys are defined with the property: "IDENTITY". In the context of the "IDENTITY" clause and SQL server I was found one old forum threat -> https://bugs.eclipse.org/bugs/show_bug.cgi?id=288015#c1 Interesting are also the remarks about IDENTITY property -> https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql?view=sql-server-ver12 If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. As we can see in the previous note (https://www.symmetricds.org/issues/view.php?id=4642#c1851) INSERT trigger should by executed "after insert". This trigger will insert values into the table "sym_data", which has on the source (corp) SQL server also defined IDENTITY property - on the field "data_id" -> [data_id] [bigint] IDENTITY(1,1) NOT NULL, |
|
|
When the problem is related with IDENTITY property, we can try to resolve problem by calling of the value of inserted.OccurenceLocalityID with: IDENT_CURRENT( 'oc_tOccurenceLocality' ) ??? |
|
|
I think I understand what you're talking about now. This came up before in issue 0002457 where an application was using @@IDENTITY and it was getting the sym_data.data_id value. Yes, I think IDENT_CURRENT( 'oc_tOccurenceLocality' ) would work. Or SCOPE_IDENTITY() might also work. |
|
|
Thank yout very much for your time and help. It was very helpfull for me, to see threats:
I also set custom_before_insert_text and custom_on_insert_text with BackupIdentitySeederFunc. After this I activated sync_on_insert to all replicated tables and .. all it's OK Problem SOLVED. Thank you. |
|
|
Could you provide your custom before and after and we can look into integrating it into the codebase? |
|
|
This code is already published in the threads:
custom_before_insert_text DECLARE @BackupIdentitySeederFunc VARCHAR(1000); SET @BackupIdentitySeederFunc ='DECLARE @BackupIdentity TABLE(IdentityID INT IDENTITY('+CAST(@@IDENTITY AS VARCHAR)+', 1)) INSERT @BackupIdentity DEFAULT VALUES';custom_on_insert_text EXEC(@BackupIdentitySeederFunc) It's good idea integrate this code into the codebase. Best regards Stanley |
|
|
Sorry for corrupted html tags with missing quotes in the previos note. I wanted write: |
- Anonymous