Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-12-27 : 08:11:44
|
Hi,I'm trying to program a trigger that writes all changes(like del,ins,upd) on the table MSSQL_TAB to the Oralce table ORA_CHANGES. .It's allways an insert to the Table ORA_CHANGES and the field DS_TYPE is used as a status field for insert=i, update=u and delete=d.Here is my attempt but it does not work. I'm not sure if it is so at all.Can anyone help me, to get a working trigger ? THXTable MS-SQL Server 2008CREATE TABLE [dbo]. MSSQL_TABDS_TYPE CHAR (1)DT_STATUS BITDW_DOC_ID INTDW_MOD_DATE DATETIMEDW_ARCHIVE INTOracle tableCREATE TABLE ORA_CHANGED( DS_TYPE VARCHAR2 (1 BYTE) DEFAULT '-' NOT NULL, DS_TABLE VARCHAR2 (100 BYTE) DEFAULT '---' NOT NULL, DW_DOC_ID NUMBER DEFAULT -1 DW_MOD_DATE VARCHAR2 (20 CHAR), DW_ARCHIVE NUMBER (3))CREATE TRIGGER [dbo].[INSUPDDEL_Test] ON [dbo].[MSSQL_TAB]FOR INSERT, UPDATE, DELETEASDECLARE @action char(1)DECLARE @DS_TYPE CHAR(1)DECLARE @DT_STATUS BITDECLARE @DW_DOC_ID INTDECLARE @DW_MOD_DATE DATETIMEDECLARE @DW_ARCHIVE INTIF COLUMNS_UPDATED() > 0 -- insert or updateBEGIN IF EXISTS (SELECT * FROM DELETED) -- update SET @action = 'U' insert into openquery([VLINK], 'select DS_TYPE,DW_DOC_ID,DW_MOD_DATE,DW_ARCHIVE from ORA_CHANGED') SELECT @action, [DWDOCID], getdate(),[ProdUnit] from inserted --ELSE -- SET @action = 'I' --END--ELSE -- delete-- SET @action = 'D'end |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-12-28 : 02:12:48
|
See SELECT @action, [DWDOCID], getdate(),[ProdUnit] from inserted There is no [ProdUnit] column in mssql table and also [DWDOCID] should be [DW_DOC_ID]--Chandu |
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-12-28 : 09:24:19
|
Hi,oh yes is my mistake it happens by copying. But the Tabels are same ever Field are present on both side. Now the trigger is compiled and on fire a get this msg:The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "xxx_DIST" was unable to begin a distributed transaction.OLE DB provider "OraOLEDB.Oracle" for linked server "xxx_DIST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".I open the Linked-connection with this parameter !!Provider=OraOLEDB.Oracle;Data Source=MyOracleDB;User Id=master;Password=pw;DistribTX=1Any idea what this means ?Here new Trigger:USE [DWData];GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GOALTER TRIGGER [dbo].[INSUPDDEL_Test1] ON [dbo].[TEST]FOR INSERT, UPDATE, DELETEASDECLARE @action char(1)DECLARE @DS_TYPE CHAR(1)DECLARE @DT_STATUS BITDECLARE @DW_DOC_ID INTDECLARE @DW_MOD_DATE DATETIMEDECLARE @DW_ARCHIVE INTIF COLUMNS_UPDATED() > 0 -- insert or updateBEGIN IF EXISTS (SELECT * FROM DELETED) -- update SET @action = 'U' insert into openquery([VPHIN_DIST],'select DS_TYPE,DW_DOC_ID,DW_MOD_DATE,DW_ARCHIVE from BRIX.T_SI_CHANGED') SELECT @action, [DWDOCID], getdate(),[ProdUnit] from inserted; --ELSE -- SET @action = 'I' --END--ELSE -- delete-- SET @action = 'D'endGOquote: Originally posted by bandi See SELECT @action, [DWDOCID], getdate(),[ProdUnit] from inserted There is no [ProdUnit] column in mssql table and also [DWDOCID] should be [DW_DOC_ID]--Chandu
|
|
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2012-12-28 : 10:48:36
|
[quote]Originally posted by zero1de Hi,oh yes it was my mistake - it happens by copying the text. o.k now the trigger is compiled and fire with "After Insert,Update,Delete" and i get this msg !? The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "link_DIST" was unable to begin a distributed transaction.OLE DB provider "OraOLEDB.Oracle" for linked server "link_DIST" returned message "New transaction cannot enlist in the specified transaction coordinator. ".on MS SQL Server i set the security for MSDC "Allow Remote Client", "Allow Inbound" and "Allow Outbund" options are enabled.THX guysHere is the Trigger Code:USE [dwdata];GOSET ANSI_NULLS ON;GOSET QUOTED_IDENTIFIER ON;GOALTER TRIGGER [dbo].[TRG_INSUPDDEL_FRANCE] ON [dbo].[FRANCE]AFTER INSERT, UPDATE, DELETEASDECLARE @action char(1)DECLARE @DS_TYPE CHAR(1)DECLARE @DT_STATUS BITDECLARE @DW_DOC_ID INTDECLARE @DW_MOD_DATE DATETIMEDECLARE @DW_ARCHIVE INTIF COLUMNS_UPDATED() > 0 -- insert or updateBEGIN IF EXISTS (SELECT * FROM DELETED) -- update SET @action = 'U' insert into openquery([VPHIN],'select DS_TYPE,DW_DOC_ID,DW_MOD_DATE,DW_ARCHIVE from BRIX.T_SI_CHANGED') SELECT @action, [DWDOCID], getdate(),[ProdUnit] from inserted; --ELSE -- SET @action = 'I' --END--ELSE -- delete-- SET @action = 'D'endGO |
|
|
|
|
|
|
|