如:将原有的abc后边加上123.
select 'abc'+'123'结果:
如果是要修改原表:
则:
update 表名 set 字段名=字段名+'要添加字符串'首先将表job的主键拆开为日期和序号两部分CREATE TABLE job (
j_date char(8) NOT NULL DEFAULT (CONVERT(CHAR(8),getdate(),112)),
j_sn char(3) NOT NULL DEFAULT '000',
jobdscrpt varchar(50) NULL )
GO
ALTER TABLE job ADD CONSTRAINT [PK_job] PRIMARY KEY
NONCLUSTERED (j_date, j_sn)
其次建立一个表存放当前的日期和当天最大的流水号(这个表还可以存其它表的
某些列的最大值)
CREATE TABLE keys (type varchar (20) PRIMARY KEY ,value varchar (20)
NOT NULL )
然后向表keys中插入表示当前日期和最大序号的行
INSERT INTO keys (type, value) VALUES('j_date', '')
GO
INSERT INTO keys (type, value) VALUES('j_sn',0)
最后是建立触发器
CREATE TRIGGER trg_autojobid on job for insert as
declare @cur_date char(8), @max_value char(6)
select @cur_date = convert(char(8),getdate(),112)
if not exists (select value from keys where type = 'j_date' and
value = @cur_date)
begin
update keys set value = @cur_date where type = 'j_date'
update keys set value = '000' where type = 'j_sn'
end
select @max_value=right('000'+ltrim(str(convert(int,value)+1)),3)
from keys where type='j_sn'
update job set j_sn = @max_value where j_sn = '000'
update keys set value = @max_value where type = 'j_sn'
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)