| Author |
 Topic  |
|
|
zero1de
Yak Posting Veteran
Germany
66 Posts |
 Posted - 12/27/2012 : 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 ?
THX
Table MS-SQL Server 2008
CREATE TABLE [dbo]. MSSQL_TAB
DS_TYPE CHAR (1)
DT_STATUS BIT
DW_DOC_ID INT
DW_MOD_DATE DATETIME
DW_ARCHIVE INT
Oracle table
CREATE 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, DELETE
AS
DECLARE @action char(1)
DECLARE @DS_TYPE CHAR(1)
DECLARE @DT_STATUS BIT
DECLARE @DW_DOC_ID INT
DECLARE @DW_MOD_DATE DATETIME
DECLARE @DW_ARCHIVE INT
IF COLUMNS_UPDATED() > 0 -- insert or update
BEGIN
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
|
Edited by - zero1de on 12/28/2012 01:47:30
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 12/28/2012 : 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
Yak Posting Veteran
Germany
66 Posts |
Posted - 12/28/2012 : 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=1
Any idea what this means ?
Here new Trigger:
USE [DWData];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER TRIGGER [dbo].[INSUPDDEL_Test1] ON [dbo].[TEST]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @action char(1)
DECLARE @DS_TYPE CHAR(1)
DECLARE @DT_STATUS BIT
DECLARE @DW_DOC_ID INT
DECLARE @DW_MOD_DATE DATETIME
DECLARE @DW_ARCHIVE INT
IF COLUMNS_UPDATED() > 0 -- insert or update
BEGIN
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'
end
GO
quote:
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
|
Edited by - zero1de on 12/28/2012 09:25:19 |
|
|
|
zero1de
Yak Posting Veteran
Germany
66 Posts |
Posted - 12/28/2012 : 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 guys
Here is the Trigger Code:
USE [dwdata];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER TRIGGER [dbo].[TRG_INSUPDDEL_FRANCE] ON [dbo].[FRANCE]
AFTER INSERT, UPDATE, DELETE
AS
DECLARE @action char(1)
DECLARE @DS_TYPE CHAR(1)
DECLARE @DT_STATUS BIT
DECLARE @DW_DOC_ID INT
DECLARE @DW_MOD_DATE DATETIME
DECLARE @DW_ARCHIVE INT
IF COLUMNS_UPDATED() > 0 -- insert or update
BEGIN
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'
end
GO
|
Edited by - zero1de on 12/28/2012 11:09:44 |
|
|
| |
Topic |
|
|
|
Insert,Update,Delete Trigger
