mysql auto increment in yy mm xxx format

1 /****** Object:  Trigger [dbo].[customAutoIncrement]
2
3        SET ANSI_NULLS ON
4        GO
5
6        SET QUOTED_IDENTIFIER ON
7        GO
8
9         CREATE TRIGGER [dbo].[customAutoIncrement]
10         ON [dbo].[Products_SumOrders]
11        AFTER INSERT
12        AS 
13        BEGIN
14            SET NOCOUNT ON;
15            declare @maxOrderID int;
16            declare @maxOrderRef nvarchar(max);
17            declare @relativeID varchar(max);
18            declare @orderId int;
19            select @orderId =orderid from inserted;
20            print(@orderId);--allow me to display the inserted order id
21            SET @maxOrderID = (SELECT Max(orderid) FROM [Products_SumOrders] WHERE MONTH([OrderDate]) = (MONTH(GETDATE())) AND YEAR([OrderDate]) = YEAR(GETDATE()) and orderid < @orderId );
22                print(@maxOrderID);--last added order
23    --custom month format (always MM):
24                declare @mon int;
25                declare @stringMon nvarchar(10);
26                set @mon= MONTH(GETDATE());
27                IF @mon <10
28                set @stringMon ='0' +  CONVERT(VARCHAR(1),@mon) ;
29                ELSE
30                set @stringMon = CONVERT(VARCHAR(2),@mon) ;
31            IF @maxOrderID is null --no orders has been added in this month:
32            begin
33                set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-001';
34            end
35            ELSE
36--custom order id format (always XXX):
37            begin
38            set @relativeID =(SELECT [purchaseOrderRef] FROM [Products_SumOrders] WHERE orderid=@maxOrderID);
39            set @relativeID = Substring(@relativeID,LEN(@relativeID)-(Charindex('-', REVERSE(@relativeID))-2),LEN(@relativeID));
40            print(CONVERT(int,@relativeID));
41            IF CONVERT(int,@relativeID) < 9
42                set @relativeID ='00' +  CONVERT(VARCHAR(2),@relativeID+1) ;
43                ELSE
44                begin
45                if CONVERT(int,@relativeID) < 99
46                set @relativeID ='0' +  CONVERT(VARCHAR(3),@relativeID+1) ;
47                else
48                set @relativeID = CONVERT(VARCHAR(3),@relativeID+1) ;
49                end
50            set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-'+ CONVERT(VARCHAR(3),@relativeID);
51            end
52            print(@maxOrderRef);
53            UPDATE Products_SumOrders
54            SET purchaseOrderRef =  @maxOrderRef
55            FROM inserted  INNER JOIN [Products_SumOrders]  On inserted.orderid = [Products_SumOrders].orderid
56        END 
57
58        GO