流水号-sql触发器

DECLARE @DATE VARCHAR(20)
DECLARE @MAXORDER INT
DECLARE @SN INT
SELECT @DATE=SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),112),1,6)–201611
SELECT @MAXORDER=MAX(CAST(SUBSTRING(RecordId,LEN(RecordId)-3,4) AS INT))
FROM YWUE_1 WHERE @DATE=SUBSTRING(RecordId,3,6)

IF @MAXORDER IS NULL
SET @MAXORDER=0

SELECT @SN=@MAXORDER+1
UPDATE YWUE_1 SET RecordId=‘XSY’+@DATE+CASE LEN(@SN) WHEN 1 THEN ‘000’+CAST(@SN as char(1))
WHEN 2 THEN ‘00’+CAST(@SN as char(2))
WHEN 3 THEN ‘0’+CAST(@SN as char(3))
WHEN 4 THEN CAST(@SN as CHAR(4)) END
WHERE TASKID=@TASKID AND ISNULL(RecordId,’’)=’’