我想了一个办法供你参考。首先将表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'上述语句我在MS SQL7上调试通过。
我想了一个办法,和NONE差不多。而且在处理时只能处理一行INSERT的情况。想法是这样的:在插入时,判断行数是否为一行,若是的话,在序号表中取值来更新字符串值。 /****** Object: Trigger dbo.tr_charid_insert Script Date: 00-2-21 13:10:01 ******/ if exists (select * from sysobjects where id = object_id('dbo.tr_charid_insert') and sysstat & 0xf = 8) drop trigger dbo.tr_charid_insert GO/****** Object: Table dbo.charid Script Date: 00-2-21 13:10:01 ******/ if exists (select * from sysobjects where id = object_id('dbo.charid') and sysstat & 0xf = 3) drop table dbo.charid GO/****** Object: Table dbo.id Script Date: 00-2-21 13:10:01 ******/ if exists (select * from sysobjects where id = object_id('dbo.id') and sysstat & 0xf = 3) drop table dbo.id GO/****** Object: Table dbo.charid Script Date: 00-2-21 13:10:01 ******/ CREATE TABLE dbo.charid ( charid char (11) NOT NULL , intnull int IDENTITY (1, 1) NOT NULL , CONSTRAINT PK_charid_1__10 PRIMARY KEY CLUSTERED ( charid ) ) GO/****** Object: Table dbo.id Script Date: 00-2-21 13:10:02 ******/ CREATE TABLE dbo.id ( id int NOT NULL ) GO/****** Object: Trigger dbo.tr_charid_insert Script Date: 00-2-21 13:10:02 ******/ CREATE TRIGGER tr_charid_insert ON dbo.charid FOR INSERT AS declare @id varchar(3),@row int select @row=@@rowcount if (@row>1) begin print "Can't insert more than one row" rollback transaction end else begin update id set id=id+1 select @id=right('000'+ltrim(str(id+1)),3) from id update charid set c.charid=convert(varchar(8),getdate(),112)+convert(varchar(3),@id) from charid c,inserted i where c.intnull=i.intnull end GO 其中ID表做为序号发生器,以保证序号连续,如果对连续要求不高,可用IDETITY类型代替。 我写了个SQL来验证这个触发器。 begin tran insert charid(charid) values('1') insert charid(charid) values('1') select * from charid rollback tran
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'上述语句我在MS SQL7上调试通过。
/****** Object: Trigger dbo.tr_charid_insert Script Date: 00-2-21 13:10:01 ******/
if exists (select * from sysobjects where id = object_id('dbo.tr_charid_insert') and sysstat & 0xf = 8)
drop trigger dbo.tr_charid_insert
GO/****** Object: Table dbo.charid Script Date: 00-2-21 13:10:01 ******/
if exists (select * from sysobjects where id = object_id('dbo.charid') and sysstat & 0xf = 3)
drop table dbo.charid
GO/****** Object: Table dbo.id Script Date: 00-2-21 13:10:01 ******/
if exists (select * from sysobjects where id = object_id('dbo.id') and sysstat & 0xf = 3)
drop table dbo.id
GO/****** Object: Table dbo.charid Script Date: 00-2-21 13:10:01 ******/
CREATE TABLE dbo.charid (
charid char (11) NOT NULL ,
intnull int IDENTITY (1, 1) NOT NULL ,
CONSTRAINT PK_charid_1__10 PRIMARY KEY CLUSTERED
(
charid
)
)
GO/****** Object: Table dbo.id Script Date: 00-2-21 13:10:02 ******/
CREATE TABLE dbo.id (
id int NOT NULL
)
GO/****** Object: Trigger dbo.tr_charid_insert Script Date: 00-2-21 13:10:02 ******/
CREATE TRIGGER tr_charid_insert ON dbo.charid
FOR INSERT
AS
declare @id varchar(3),@row int
select @row=@@rowcount
if (@row>1)
begin
print "Can't insert more than one row"
rollback transaction
end
else
begin
update id set id=id+1
select @id=right('000'+ltrim(str(id+1)),3) from id
update charid set c.charid=convert(varchar(8),getdate(),112)+convert(varchar(3),@id) from charid c,inserted i where c.intnull=i.intnull
end
GO
其中ID表做为序号发生器,以保证序号连续,如果对连续要求不高,可用IDETITY类型代替。
我写了个SQL来验证这个触发器。
begin tran
insert charid(charid) values('1')
insert charid(charid) values('1')
select * from charid
rollback tran