USE [db_edits]
GO
/****** Object:  Trigger [dbo].[CreateTransTypeCode]    Script Date: 02/06/2018 13:47:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-02-06
ALTER TRIGGER [dbo].[CreateTransTypeCode] ON [dbo].[m_transaction_type]
	FOR INSERT 
AS 

BEGIN

	SET NOCOUNT ON;

	--DECLARE @rownums VARCHAR(5);
	--DECLARE @code VARCHAR(8);

 --   SET @rownums = (
	--	SELECT CAST((COUNT(transaction_type_id)) AS VARCHAR) 
	--	FROM m_transaction_type
	--);

	--SET @code = (SELECT 'T' + STUFF('000000', 6 - LEN(@rownums) + 1, LEN(@rownums), @rownums));
	
	DECLARE 
		@last_transaction_type_code VARCHAR(7),
		@new_transaction_type_code VARCHAR(7)

	IF EXISTS(
							SELECT
								*
							FROM
								dbo.m_transaction_type
							WHERE
								COALESCE(transaction_type_code,'') <> ''
						)
		BEGIN
			SELECT
				@last_transaction_type_code = MAX(transaction_type_code)
			FROM
				dbo.m_transaction_type
			WHERE
				COALESCE(transaction_type_code,'') <> ''

			SET @new_transaction_type_code = LEFT(@last_transaction_type_code,1) + RIGHT('000000' + CAST(CAST(RIGHT(@last_transaction_type_code,6) AS INTEGER) + 1 AS VARCHAR(6)),6)
		END
	ELSE
		SET @new_transaction_type_code = 'T000001'	-- initial

	UPDATE 
		dbo.m_transaction_type
	SET 
		transaction_type_code = @new_transaction_type_code
	WHERE 
		transaction_type_id IN (
														SELECT 
															transaction_type_id 
														FROM 
															inserted
														);

END
