Error on Delete request

  • Sebastien
  • Topic Author
  • Visitor
  • Visitor
10 years 1 week ago #10951 by Sebastien
Error on Delete request was created by Sebastien
Hello,

We try to prepare some triggers to able to synchronize 2 different tables ( always from MSSQL to PostgreSql ). This synchronization is done only when the AttributeName column is equal to 'Service'.

On MSSQL, we have a table with the following attributes :
Code:
CREATE TABLE [dbo].[StringMaps]( [StringMapId] [uniqueidentifier] NOT NULL, [AttributeName] [nvarchar](100) NOT NULL, [AttributeValue] [int] NOT NULL, [DisplayOrder] [int] NOT NULL, [ObjectTypeCode] [int] NOT NULL, [ShortValue] [nvarchar](255) NOT NULL, [Value] [nvarchar](255) NOT NULL, [CreatedBy] [uniqueidentifier] NULL, [CreatedOn] [datetime] NULL, [ModifiedBy] [uniqueidentifier] NULL, [ModifiedOn] [datetime] NULL, PRIMARY KEY CLUSTERED ( [StringMapId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

On PostgreSql, we have :
Code:
CREATE TABLE oto_referentiel.service ( id serial NOT NULL, code_oto character varying(15), code_bddrh character varying(255), libelle character varying(255), u_creat character varying(20), d_creat date NOT NULL, u_maj character varying(20), d_maj date, id_bdd_rh character varying(64), CONSTRAINT pk_service PRIMARY KEY (id) ) WITH ( OIDS=FALSE )

I have created 3 triggers on events INSERT, UPDAT and DELET.

The first and the second works well but the event "delete" never work.

Insert Trigger :
Code:
CREATE TRIGGER TRIGGER_SERVICE_INSERT ON StringMaps FOR INSERT AS BEGIN DISTRIBUTED TRANSACTION IF EXISTS(SELECT * FROM Inserted where AttributeName = 'Service') BEGIN INSERT OPENQUERY(wake, 'select code_oto, code_bddrh, libelle, id_bdd_rh, u_creat, d_creat from oto_referentiel.service') SELECT AttributeName + '_' + CAST(AttributeValue AS VARCHAR), ShortValue, Value, CAST(StringMapId AS VARCHAR(64)), 'MSSQL', CURRENT_TIMESTAMP FROM Inserted where AttributeName = 'Service'; END COMMIT TRANSACTION;

Update Trigger :
Code:
CREATE TRIGGER TRIGGER_SERVICE_UPDATE ON StringMaps FOR UPDATE AS BEGIN DISTRIBUTED TRANSACTION IF EXISTS(SELECT * FROM Updated where AttributeName = 'Service') BEGIN UPDATE OPENQUERY(wake, 'select code_oto, code_bddrh, libelle, u_maj, d_maj, id_bdd_rh from oto_referentiel.service') SET code_oto=AttributeName + '_' + CAST(AttributeValue AS VARCHAR), code_bddrh=ShortValue, libelle=Value, u_maj='MSSQL', d_maj=CURRENT_TIMESTAMP FROM Inserted WHERE AttributeName = 'Service' and id_bdd_rh=CAST(StringMapId AS VARCHAR(64)); END COMMIT TRANSACTION;

Delete Trigger : (which doesn't work)
Code:
CREATE TRIGGER TRIGGER_SERVICE_DELETE ON StringMaps FOR DELETE AS BEGIN DISTRIBUTED TRANSACTION IF EXISTS(SELECT * FROM Deleted where AttributeName = 'Service') BEGIN DELETE OPENQUERY(wake, 'select id_bdd_rh from oto_referentiel.service') WHERE id_bdd_rh in ( select CAST(StringMapId AS VARCHAR(64)) from Deleted where AttributeName = 'Service') END COMMIT TRANSACTION;

When I delete a row on StringMaps table on MSSQL, I have this error :

Msg 7394, Niveau 16, État 2, Procédure TRIGGER_SERVICE_DELETE, Ligne 5
Le fournisseur OLE DB "PGNP" du serveur lié "wake" a rapporté une erreur lors de la validation de la transaction en cours.


I can't understand this error.

I launched the PGNP Profiler, and in attached file, here the log about this error.

Servers configuration :

- MSSQL : Microsoft SQL Server Standard Edition (64-bit) 2008 R2
- PGNP : PGNP-Postgres-SE-Trial-1.4.0.3232 ( We have a licence but, since our production environment crash with PGNP, we prefer test on another server first ).
- PostgreSql : PostgreSQL 8.4.11 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit

Currently, we are blocked because each time a row is deleted, the error appear, and the following request give also a error.

Thanks for advance for your help.

Best Regards.

P.S. : Some messages are in french messages.
Attachments:

Please Log in or Create an account to join the conversation.

More
10 years 6 days ago #10952 by Moderator
Replied by Moderator on topic Re: Error on Delete request
We are working on the issue. We will update the thread as soon as we have more information. Thank you for the very detailed info!

Please Log in or Create an account to join the conversation.

  • Sebastien
  • Topic Author
  • Visitor
  • Visitor
9 years 11 months ago #10955 by Sebastien
Replied by Sebastien on topic Re: Error on Delete request
Hello,

Do you have a solution or workaround about this bug ?

Currently, our production is blocked.

Thanks for advance.
Best Regards.

Please Log in or Create an account to join the conversation.

  • Sebastien
  • Topic Author
  • Visitor
  • Visitor
9 years 11 months ago #10984 by Sebastien
Replied by Sebastien on topic Re: Error on Delete request
Up.

Please Log in or Create an account to join the conversation.

  • Sebastien
  • Topic Author
  • Visitor
  • Visitor
9 years 11 months ago #11040 by Sebastien
Replied by Sebastien on topic Re: Error on Delete request
A solution ??

Please Log in or Create an account to join the conversation.

More
9 years 11 months ago #11086 by Moderator
Replied by Moderator on topic Re: Error on Delete request
Sorry for the delay. Yes, we published new release for the bug: 1.4.0.3248. Please run PGNPUpdate utility to update to 3248, or later build.

Please Log in or Create an account to join the conversation.

Time to create page: 0.162 seconds
Powered by Kunena Forum