SqlServer 同步到oracle 数据库触发器insert

秒速五厘米 2024-04-17 18:07 116阅读 0赞

USE [FenHotel]
    GO
    /****** Object:  Trigger [dbo].[order_insert]    Script Date: 08/23/2019 15:16:06 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,huQk>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER TRIGGER [dbo].[order_insert]
       ON  [dbo].[T_HotelOrder]
       AFTER INSERT
    AS 
    BEGIN
    
        declare @order_id varchar(50);
        declare @order_json varchar(MAX);
        declare @order_state int;
        declare @create_time datetime;
        declare @submit_time datetime;
        declare @cancel_time datetime;
        declare @dist_order_id varchar(50);
        declare @dist_uuid varchar(50);
        declare @creater varchar(50);
        declare @currency_code varchar(10);
        declare @amount money;
        declare @pay_amount money;
        declare @pay_time datetime;
        declare @order_type int;
        declare @memo varchar(MAX);
        --declare @keyId int;
        declare @fen_order_id varchar(50);
    	declare @hotel_id varchar(50);
    	declare @hotel_name varchar(50);
    	declare @room_id varchar(50);
    	declare @room_name varchar(50);
    	declare @rate_plan_id varchar(50);
    	declare @rate_plan_name varchar(50);
    	declare @booking_code varchar(50);
    	declare	@prepaid_indicator varchar(50);
    	declare @prepaid_indicator_flag int;				
    	declare @number_of_units int;
    	declare @start_date datetime;
    	declare @end_date datetime;
    	declare @guest_count int;
    	declare @customers varchar(50);
    	declare @contact_name varchar(50);
    	declare @contact_telphone varchar(100);
    	declare	@contact_email	varchar(100);	
    	declare	@arrival_time	datetime;	
    	declare	@order_state_sync_flag	int;	
    	declare	@last_update_state_time	datetime;	
    	declare	@order_id_501	varchar(50);	
    	declare	@order_id_502	varchar(50);	
    	declare	@order_id_507	varchar(50);	
    	declare	@order_id_509	varchar(50);	
    	declare	@cancel_reason	varchar(50);	
    	declare	@manual_flag	int;	
    	declare	@manual_reason	varchar(50);	
    	declare	@manual_done	int;
    
    			select	@order_id=order_id,@order_json=order_json,@order_state=order_state,@create_time=create_time,@submit_time =submit_time,
    					@cancel_time=cancel_time,@dist_order_id=dist_order_id,@dist_uuid=dist_uuid,@creater=creater,@currency_code=currency_code,
    					@amount = amount,@pay_amount=pay_amount,@pay_time=pay_time,@order_type=order_type,@memo=memo,@fen_order_id=fen_order_id,
    					@hotel_id=hotel_id,@hotel_name=hotel_name,@room_id =room_id,@room_name=room_name,@rate_plan_id=rate_plan_id,@rate_plan_name=rate_plan_name,
    					@booking_code=booking_code,@prepaid_indicator=prepaid_indicator,@number_of_units=number_of_units,@start_date=start_date,
    					@end_date=end_date,@guest_count=guest_count,@customers=customers,@contact_name=contact_name,@contact_telphone=contact_telphone,
    					@contact_email=contact_email,@arrival_time=arrival_time,@order_state_sync_flag=order_state_sync_flag,@last_update_state_time=last_update_state_time,
    					@order_id_501=order_id_501,@order_id_502=order_id_502,@order_id_507=order_id_507,@order_id_509=order_id_509,@cancel_reason=cancel_reason,
    					@manual_flag=manual_flag,@manual_reason=manual_reason,@manual_done=manual_done from inserted;
    
    IF @prepaid_indicator='True'
    	BEGIN
    	set @prepaid_indicator_flag=1;
    	END
    ELSE IF @prepaid_indicator='False'
    	BEGIN
    	set @prepaid_indicator_flag=0;
    	END	
    		
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	--SET NOCOUNT ON;
    SET XACT_ABORT on INSERT OPENQUERY (ORACLE, 
    'SELECT ORDER_ID,ORDER_JSON,ORDER_STATE,CREATE_TIME,SUBMIT_TIME,CANCEL_TIME,DIST_ORDER_ID,DIST_UUID,CREATER,CURRENCY_CODE,
    		AMOUNT,PAY_AMOUNT,PAY_TIME,ORDER_TYPE,MEMO,FEN_ORDER_ID,HOTEL_ID,HOTEL_NAME,ROOM_ID,ROOM_NAME,RATE_PLAN_ID,
    		RATE_PLAN_NAME,BOOKING_CODE,PREPAID_INDICATOR,NUMBER_OF_UNITS,START_DATE,END_DATE,GUEST_COUNT,CUSTOMERS,CONTACT_NAME,
    		CONTACT_TELPHONE,CONTACT_EMAIL,ARRIVAL_TIME,ORDER_STATE_SYNC_FLAG,LAST_UPDATE_STATE_TIME,
    		ORDER_ID_501,ORDER_ID_502,ORDER_ID_507,ORDER_ID_509,CANCEL_REASON,MANUAL_FLAG,MANUAL_REASON,MANUAL_DONE 										
    		FROM FN_TICKET.HT_ORDER')
    values(@order_id,@order_json,@order_state,@create_time,@submit_time,@cancel_time,@dist_order_id,@dist_uuid,@creater,@currency_code,@amount,@pay_amount,@pay_time,@order_type,
    		@memo,@fen_order_id,@hotel_id,@hotel_name,@room_id,@room_name,@rate_plan_id,@rate_plan_name,@booking_code,@prepaid_indicator_flag,@number_of_units,@start_date,
    		@end_date,@guest_count,@customers,@contact_name,@contact_telphone,@contact_email,@arrival_time,@order_state_sync_flag,@last_update_state_time,@order_id_501,
    		@order_id_502,@order_id_507,@order_id_509,@cancel_reason,@manual_flag,@manual_reason,@manual_done);
       
    
    END