
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- by Pribadi Pamungkas. 2017-07-14
CREATE TRIGGER dbo.trig_m_form 
   ON  dbo.m_eform
   INSTEAD OF INSERT
AS 
BEGIN
	
	SET NOCOUNT ON; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	
	DECLARE @transaction_type_id INT
	
	SELECT
	  @transaction_type_id = transaction_type_id
	FROM INSERTED
	
	DECLARE 
    @last_eform_code VARCHAR(8),
    @new_eform_code VARCHAR(8)
  SELECT
	  @last_eform_code = MAX(eform_code)
  FROM
	  dbo.m_eform
  WHERE
    transaction_type_id = @transaction_type_id
  IF @last_eform_code IS NULL
    BEGIN
      SELECT
    	  @last_eform_code = 'F' + mtc.transaction_category_code + '00000'
      FROM
    	  m_transaction_type AS mtt INNER JOIN 
    	  m_transaction_category mtc ON mtc.transaction_category_id = mtt.transaction_category_id
      WHERE
        mtt.transaction_type_id = @transaction_type_id
    END

  SET @new_eform_code = LEFT(@last_eform_code,3) + RIGHT('00000' + CAST(CAST(RIGHT(COALESCE(@last_eform_code,'0'),5) AS INTEGER) + 1 AS VARCHAR(5)),5)

  INSERT INTO [dbo].[m_eform]
           ([eform_code]
           ,[transaction_type_id]
           ,[is_need_surat_kuasa]
           ,[remarks]
           ,[status_id]
           ,[created_by]
           ,[created_date]
           ,[modified_by]
           ,[modified_date]
           ,[is_delete])
    SELECT @new_eform_code
          ,[transaction_type_id]
          ,[is_need_surat_kuasa]
          ,[remarks]
          ,[status_id]
          ,[created_by]
          ,[created_date]
          ,[modified_by]
          ,[modified_date]
          ,[is_delete]
      FROM INSERTED

END
GO


