-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER TIG_IST_CUT_MTL_TYPE
ON CUT_MTL_TYPE
AFTER insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE
@new_mtl_type_no VARCHAR(20),
@max_num INT,
@ID VARCHAR(500)
-- Insert statements for trigger here
SELECT @id = id FROM inserted
SELECT @max_num = ISNULL(CAST( REPLACE(MAX(mtl_type_no),'MT','') AS INT),0)
FROM cut_mtl_type
WHERE mtl_type_no LIKE 'MT%'
SET @max_num = @max_num + 1
SET @new_mtl_type_no = 'MT'+ CASE WHEN @max_num > 9 AND @max_num <=99 THEN
'0'+ CAST(@max_num AS VARCHAR(10))
WHEN @max_num > 99 THEN CAST(@max_num AS VARCHAR(10))
ELSE '00'+CAST(@max_num AS VARCHAR(10)) END
UPDATE CUT_MTL_TYPE
SET mtl_type_no = @new_mtl_type_no
WHERE id = @id
END 轉載于:https://www.cnblogs.com/baili-luoyun/p/11151409.html