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.
Author |
Topic |
zero1de
Posting Yak Master
105 Posts |
Posted - 2013-01-10 : 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 UPDATEASinsert 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
212 Posts |
|
zero1de
Posting Yak Master
105 Posts |
Posted - 2013-01-16 : 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, UpdateASdeclare @DWDOCID intdeclare @DWMODDATE DATETIMEdeclare @DWPRODUNIT INTdeclare @TYPE CHAR(1)declare @DT_STATUS datetimedeclare @PRS_DT_BIRTHset @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 |
|
|
|
|
|
|
|