Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help - MS SQL Trigger for Oracle insert


Need help - MS SQL Trigger for Oracle insert

Author
Message
xoom
xoom
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 43
Hi,

is it possible to use a ms sql trigger to insert a row in Oracle Table ? I use this way an get error Error 7391: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. I reconfig the MSDTS on the Server and allow all connection in and out but still get the error !?

Is there other way to do this ?? I want to write the triggered Data in to a Oracle Table.

THX


MS SQL Trigger !
CREATE TRIGGER [dbo].[trg_log_changes_table]
ON [dbo].[TEST]
FOR INSERT, DELETE, Update
AS
Begin Tran
BEGIN
IF EXISTS(SELECT * FROM Inserted)
BEGIN
INSERT INTO OPENQUERY (
[xxx],
'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED')
SELECT
GETDATE () AS DT_Status,
GETDATE () AS prs_dt_birth,
'I' AS DS_TYPE,
DWDOCID AS DW_DOC_ID,
PRODUNIT AS DW_ARCHIVE,
DWMODDATETIME AS DW_MOD_DATE
FROM Inserted
END
ShineBoy
ShineBoy
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 154
Triggers are hard to test and debug, have you tested the insert string outside of the trigger (obviously needs a bit of rewriting) ?
Your problem sounds like less to do with the fact that it is in a trigger and more to do with the actual insert.
I had a similar hiccup when trying to write to MySQL, turned out to be different data types related.
xoom
xoom
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 43
Hi,

the Insert Statment works alone fine i have test it. I get the error with the Trigger !!
xoom
xoom
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 43
I think so in this style it would work but I have no idea with dynamic SQL can anyone help me here?
What is missing in the trigger?


CREATE TRIGGER [dbo].[trg_log_changes_france4]
ON [dbo].[TEST]
FOR INSERT, DELETE, Update
AS

declare @DWDOCID int
declare @DWMODDATE DATETIME
declare @DWPRODUNIT INT
declare @TYPE CHAR(1)
declare @DT_STATUS datetime
declare @PRS_DT_BIRTH


set @DWDOCID = (SELECT DWDOCID FROM inserted)
set @DWMODDATE = (SELECT DWMODDATETIME FROM inserted)
set @DWPRODUNIT = (SELECT PRODUNIT FROM inserted)


IF EXISTS(SELECT * FROM Inserted)
BEGIN
declare @query NVARCHAR(500);
set @query ='INSERT INTO OPENQUERY([VPHIN], 'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED'';
SELECT @DT_STATUS=Getdata(), @PRD_DT_BIRTH=Getdata(), @DS_TYPE='I', @DWDOCID, @DWPRODUNIT, @DWMODDATE FROM Inserted
execute sp_executesql @query

End
nick.mcdermaid
nick.mcdermaid
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 766
This is not going to scale.

This statement:

'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED

Is selecting ALL of the rows from T-SI_CHANGED.... before you even insert anything.

So what happens after a month when T_SI_CHANGED has a zillion records and your trigger is run a dozen times to insert some records? It selects the entire T_SI_CHANGED table a dozen times on the Oracle side.


Possible solutions to this:

1. Change it to this, so that no rows need to be selected on the Oracle side

'select dt_status,prs_dt_birth, DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from owner.T_SI_CHANGED WHERE 1=0


2. Use this instead (which does not even require a select on the Oracle side:

INSERT INTO [VPHIN].owner.T_SI_CHANGED (col1,col2) SELECT COl1, Col2 from INSERTED



However linked servers in general can unreliable, especially to Oracle are unreliable. So if this Oracle insert fails, your trigger fails, and then the change on the SQL Server side failes.

I suggest that you instead write the changes to a log table on the SQL Server side, then use a scheduled job to send over the logged records to the Oracle table in a batch (via SSIS or linked servers). This disconnects the 'data transfer' part from the 'trigger' part, and will stop errors occuring when you insert on the SQL side (due to your trigger failing)
nick.mcdermaid
nick.mcdermaid
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 766
I'm pretty sure SQL Server 2005 onwards supports replication to Oracle, which is what you're trying to do.

It looks complicated but it's the 'out of the box' way to do it.

http://msdn.microsoft.com/en-us/library/ms151738(v=sql.90).aspx
xoom
xoom
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 43
Hi,
you mean I should create a trigger with (Del, ins and the upd Option) on ms sql table and then write the data to the Oracle table.
Just how can i do it, to write the data generated by the trigger to Oracle table over (SSIS-Package) ?
Can you tell me how can I do that?

THX
nick.mcdermaid
nick.mcdermaid
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 766
I suggest one of two options:

A. Use out of the box SQL to Oracle replication


OR

B. Log the changes locally and send them over in batch:

1. Create a log table on the SQL Server
2. Your trigger should write to this log table (not to Oracle). This means you won't run the risk of an DML failing in your SQL Server database due to an unreliable trigger failure
3. Create a job which periodically identifies the changed records in the log in the SQL Server and writes them accross to Oracle.

This job might call a SSIS package, or it might call a stored procedure that inserts into an Oracle linked server.


Niether of these are simple, but they will be more reliable than useing a trigger to write directly Oracle over a linked server.


The problem is that if your insert over a linked server on Oracle fails (which it might for many reasons), then the INSERT on the SQL Server will also fail.
xoom
xoom
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 43
o.k thx i have create the trigger and stored procedure on ms sql side and run in period. the sp and it
works fine thx Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search