sql server 的触发器的demo例子

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

-- 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