Insert,Update,Delete Trigger - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Insert,Update,Delete Trigger

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 ?

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

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=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

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 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources