| Author |
 Topic  |
|
|
zero1de
Yak Posting Veteran
Germany
66 Posts |
 Posted - 01/10/2013 : 07:48:05
|
I have tried all the possibilities to write data fromn a MS SQL Server via trigger to a Oracle table.The only possible way is to use OpenQuery with dynamic SQL. But I just have no idea of dynamic SQL!
My question is who can help me to get a working dynamic SQL, witch give parameter to a Oracle Function(this Function is a synonum for the the SQL Trigger)
CREATE TRIGGER [dbo].[TRG_INSUPDDEL_FRANCE1] ON [dbo].[FRANCE]
FOR UPDATE
AS
insert into openquery([VPHIN],'select DS_TYPE,DW_DOC_ID,DW_ARCHIVE, DW_MOD_DATE from BRIX.T_SI_CHANGED_BK')
Select 'I' as DS_TYPE,
as DWDOCID,
as DW_DOC_ID,
as PRODUNIT,
as DW_ARCHIVE,
as DWMODDATETIME
from inserted |
|
|
prett
Posting Yak Master
USA
166 Posts |
|
|
zero1de
Yak Posting Veteran
Germany
66 Posts |
Posted - 01/16/2013 : 07:01:57
|
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
|
 |
|
| |
Topic |
|
|
|
Openquery with dynamic SQL
