make a field auto_increment mysql
ALTER TABLE document MODIFY COLUMN document_id INT auto_incrementmake a field auto_increment mysql
ALTER TABLE document MODIFY COLUMN document_id INT auto_incrementmysql auto increment default value with prefix month and year
/****** 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
GOCopyright © 2021 Codeinu
Forgot your account's password or having trouble logging into your Account? Don't worry, we'll help you to get back your account. Enter your email address and we'll send you a recovery link to reset your password. If you are experiencing problems resetting your password contact us
