- Thank you received: 0
Error on Delete request
- Sebastien
- Topic Author
- Visitor
-
9 years 7 months 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 :
On PostgreSql, we have :
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 :
Update Trigger :
Delete Trigger : (which doesn't work)
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-
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.
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-
4.4.5, 64-bitCurrently, 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.
Please Log in or Create an account to join the conversation.
- Moderator
-
- Offline
- New Member
- Sebastien
- Topic Author
- Visitor
-
- Sebastien
- Topic Author
- Visitor
-
- Sebastien
- Topic Author
- Visitor
-
- Moderator
-
- Offline
- New Member
Time to create page: 0.172 seconds