mysql auto increment default value with prefix month and year

Solutions on MaxInterview for mysql auto increment default value with prefix month and year by the best coders in the world

Search
showing results for - "mysql auto increment default value with prefix month and year"
Alex
17 Jul 2016
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
similar questions
mysql check auto increment valuemysql update autoadd auto increment column to existing table mysqlinsert into auto increment mysqlsql server set default value equal to auto incrementmysql reset auto increment valuemysql reset auto increment to 1auto increment column in mysql query resultshow to change the auto increment in existing table mysqlzerar auto increment mysqlmysql reset auto increment idauto increment mysqlcreate table primary key auto increment mysqlmysql remove auto incrementmake a field auto increment mysqlh2 auto increment and uniquehow to set an already made tables auto increment in mysqlmysql set id auto incrementmysql min value rowmysql auto increment after deletemysql reset auto increment
queries leading to this page
mysql auto increment in yy mm xxx formatmysql auto increment in dd mm xxx formatmysql auto increment default value with prefix month and yearmysql auto increment default value with prefix month and year