USE [Waychein_Alarm]
GO
/****** Object:  Trigger [dbo].[TR_PLC]    Script Date: 2023/12/27 下午 04:15:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Gan
-- Create date: 2023-12-19
-- Description:	[tr_mo_info]輸入tr_mo_info的資料 轉入 mo_info
-- =============================================
ALTER TRIGGER [dbo].[TR_tr_mo_info]
   ON [dbo].[tr_mo_info] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- ========================================================================================================
	-- 欄位
   DECLARE @order_no varchar(15)
	DECLARE @client_no varchar(10)
	DECLARE @client_name varchar(12)
	DECLARE @mo varchar(15)
	DECLARE @material_no varchar(100)
	DECLARE @created_date datetime
	DECLARE @product_code varchar(20)
	DECLARE @product_name varchar(30)
	DECLARE @specification varchar(30)
	DECLARE @expected_start_date datetime
	DECLARE @expected_end_date datetime
	DECLARE @real_start_date datetime
	DECLARE @real_end_date datetime
	DECLARE @production_qty float
	DECLARE @completed_qty float
	DECLARE @scrap_qty float
	DECLARE @process_code varchar(10)
	DECLARE @process_name varchar(30)


	-- ========================================================================================================

	DECLARE Cursor1 CURSOR FOR
	    SELECT 
			 [MFOR003]
			,[MFOR007]
			,[SU01003]
			,[MMKB001]
			,[MMKB802]
			,[MMKB002]
			,[MMKB003]
			,[INVN005]
			,[INVN051]
			,[MMKB008]
			,[MMKB009]
			,[MMKB010]
			,[MMKB011]
			,[MMKB021]
			,[MMKB022]
			,[MMKB023]
			,[DKB1003]
			,[OSMK003]
		FROM INSERTED
		
		--For Update
	--開始 run cursor	
	OPEN Cursor1

	FETCH NEXT FROM Cursor1 INTO @order_no ,@client_no ,@client_name ,@mo ,@material_no ,@created_date ,@product_code ,@product_name ,@specification ,@expected_start_date ,@expected_end_date ,@real_start_date ,@real_end_date ,@production_qty ,@completed_qty ,@scrap_qty ,@process_code ,@process_name
	WHILE @@FETCH_STATUS = 0
    BEGIN

    	INSERT INTO [dbo].[mo_info] (
			 [order_no] 
    		,[client_no] 
    		,[client_name] 
    		,[mo] 
    		,[material_no] 
    		,[created_date] 
    		,[product_code] 
    		,[product_name] 
    		,[specification] 
    		,[expected_start_date] 
    		,[expected_end_date] 
    		,[real_start_date] 
    		,[real_end_date] 
    		,[production_qty] 
    		,[completed_qty] 
    		,[scrap_qty] 
    		,[process_code] 
    		,[process_name]
		)
    	VALUES (
    		 @order_no 
    		,@client_no 
    		,@client_name 
    		,@mo 
    		,@material_no 
    		,@created_date 
    		,@product_code 
    		,@product_name 
    		,@specification 
    		,@expected_start_date 
    		,@expected_end_date 
    		,@real_start_date 
    		,@real_end_date 
    		,@production_qty 
    		,@completed_qty 
    		,@scrap_qty 
    		,@process_code 
    		,@process_name
    	)

	--將下一筆資料填入變數
	FETCH NEXT FROM Cursor1 INTO @order_no ,@client_no ,@client_name ,@mo ,@material_no ,@created_date ,@product_code ,@product_name ,@specification ,@expected_start_date ,@expected_end_date ,@real_start_date ,@real_end_date ,@production_qty ,@completed_qty ,@scrap_qty ,@process_code ,@process_name
	END

    CLOSE Cursor1		--關閉cursor與參數的關聯
	DEALLOCATE Cursor1 	--將cursor物件從記憶體移除 

END
