求SQL SERVER 按年月自动编号的触发器！
求救，如题！ 编号格式是 CK1207-0001 “CK”是单号前缀 “1207”取年份月份 后四位"0001" 为自动编号! 谢谢，朋友们指教！本人不会触发器，还请给实例！

[解决办法]

SQL code

--下面的代码生成长度为8的编号，编号以BH开头，其余6位为流水号。--得到新编号的函数CREATE FUNCTION f_NextBH()RETURNS char(8)ASBEGIN    RETURN(SELECT 'BH'+RIGHT(1000001+ISNULL(RIGHT(MAX(BH),6),0),6) FROM tb WITH(XLOCK,PAGLOCK))ENDGO--在表中应用函数CREATE TABLE tb(BH char(8) PRIMARY KEY DEFAULT dbo.f_NextBH(),col int)--插入资料BEGIN TRAN    INSERT tb(col) VALUES(1)    INSERT tb(col) VALUES(2)    INSERT tb(col) VALUES(3)    DELETE tb WHERE col=3    INSERT tb(col) VALUES(4)    INSERT tb(BH,col) VALUES(dbo.f_NextBH(),14)COMMIT TRAN--显示结果SELECT * FROM tb/*--结果BH         col ---------------- ----------- BH000001  1BH000002  2BH000003  4BH000004  14--*/
[解决办法]
借2楼改一下
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOIF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'GetNext')BEGIN    DROP FUNCTION GetNextENDGOCREATE FUNCTION GetNext()RETURNS VARCHAR(20)ASBEGIN    RETURN(SELECT 'CK'+ RTRIM(DATEPART(YEAR,GETDATE())) + '-' + RIGHT(RTRIM(10001 + ISNULL(RIGHT(MAX(BH),4),0)),4) FROM tba WITH(XLOCK,PAGLOCK))ENDGO--在表中应用函数CREATE TABLE tba(BH VARCHAR(20) PRIMARY KEY DEFAULT dbo.GetNext(),col int)--插入资料BEGIN TRAN    INSERT tba(col) VALUES(1)    INSERT tba(col) VALUES(2)    INSERT tba(col) VALUES(3)    DELETE tba WHERE col=3    INSERT tba(col) VALUES(4)    INSERT tba(BH,col) VALUES(dbo.GetNext(),14)COMMIT TRANSELECT * FROM tbaBH    colCK2012-0001    1CK2012-0002    2CK2012-0003    4CK2012-0004    14