SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Insert,Update,Delete Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

zero1de
Yak Posting Veteran

Germany
71 Posts

Posted - 12/27/2012 :  08:11:44  Show Profile  Reply with Quote
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
2202 Posts

Posted - 12/28/2012 :  02:12:48  Show Profile  Reply with Quote
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
Go to Top of Page

zero1de
Yak Posting Veteran

Germany
71 Posts

Posted - 12/28/2012 :  09:24:19  Show Profile  Reply with Quote
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
Go to Top of Page

zero1de
Yak Posting Veteran

Germany
71 Posts

Posted - 12/28/2012 :  10:48:36  Show Profile  Reply with Quote
[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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000