/SQL/Mysql auto increment in DD-MM-XXX format

Mysql auto increment in DD-MM-XXX format

 /****** Object:  Trigger [dbo].[customAutoIncrement]

        SET ANSI_NULLS ON
        GO

        SET QUOTED_IDENTIFIER ON
        GO

         CREATE TRIGGER [dbo].[customAutoIncrement]
         ON [dbo].[Products_SumOrders]
        AFTER INSERT
        AS 
        BEGIN
            SET NOCOUNT ON;
            declare @maxOrderID int;
            declare @maxOrderRef nvarchar(max);
            declare @relativeID varchar(max);
            declare @orderId int;
            select @orderId =orderid from inserted;
            print(@orderId);--allow me to display the inserted order id
            SET @maxOrderID = (SELECT Max(orderid) FROM [Products_SumOrders] WHERE MONTH([OrderDate]) = (MONTH(GETDATE())) AND YEAR([OrderDate]) = YEAR(GETDATE()) and orderid < @orderId );
                print(@maxOrderID);--last added order
    --custom month format (always MM):
                declare @mon int;
                declare @stringMon nvarchar(10);
                set @mon= MONTH(GETDATE());
                IF @mon <10
                set @stringMon ='0' +  CONVERT(VARCHAR(1),@mon) ;
                ELSE
                set @stringMon = CONVERT(VARCHAR(2),@mon) ;
            IF @maxOrderID is null --no orders has been added in this month:
            begin
                set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-001';
            end
            ELSE
--custom order id format (always XXX):
            begin
            set @relativeID =(SELECT [purchaseOrderRef] FROM [Products_SumOrders] WHERE orderid=@maxOrderID);
            set @relativeID = Substring(@relativeID,LEN(@relativeID)-(Charindex('-', REVERSE(@relativeID))-2),LEN(@relativeID));
            print(CONVERT(int,@relativeID));
            IF CONVERT(int,@relativeID) < 9
                set @relativeID ='00' +  CONVERT(VARCHAR(2),@relativeID+1) ;
                ELSE
                begin
                if CONVERT(int,@relativeID) < 99
                set @relativeID ='0' +  CONVERT(VARCHAR(3),@relativeID+1) ;
                else
                set @relativeID = CONVERT(VARCHAR(3),@relativeID+1) ;
                end
            set @maxOrderRef =  Substring(CONVERT(VARCHAR(4),YEAR(GETDATE())),3,4)+ '-'+  CONVERT(VARCHAR(2),@stringMon)+'-'+ CONVERT(VARCHAR(3),@relativeID);
            end
            print(@maxOrderRef);
            UPDATE Products_SumOrders
            SET purchaseOrderRef =  @maxOrderRef
            FROM inserted  INNER JOIN [Products_SumOrders]  On inserted.orderid = [Products_SumOrders].orderid
        END 

        GO

Similar codes

10 votes285 views6 answers
Sql between
SQL
-1 vote266 views1 answer
Django.core.exceptions.ImproperlyConfigured: mysqlclient 1.3.13 or newer is required; you have 0.8.0.
SQL
6 votes297 views4 answers
Mysql group by date
SQL
0 vote360 views1 answer
Mysql show schema
SQL
9 votes284 views1 answer
Alter table column change data type to text mysql
SQL
13 votes257 views3 answers
Sql online code
SQL
1 vote292 views1 answer
Table valued functions in sql
SQL
2 votes272 views1 answer
Get first n letter of department name in sql
SQL
0 vote293 views1 answer
Psql get table data types
SQL
-1 vote303 views1 answer
Add 10 to all numbers in a column sql
SQL