你的数据库设计有问题 事实上,这个ID号应该是一个计算字段 物理字段应该为(T-SQL) FId int Identity(1,1) not null, FDate smalldatetime default GetDate() not null, constraint Pk_Table Primary Key (FDate, FId) 根本不用触发器
或者这样设计数据库(其中命名自定) FId int not null, FDate smalldatetime default GetDate() not null constraint Pk_Table Primary key (FDate, FId)create trigger tr_UpdateTable on TableName For INSERT As declare iNum int if @@rowcount > 1 return update inserted set Fid = (select Fid +1 from TableName where FDate = GetDate())
什么是计算字段?是自动增长吗?那在sqlserver中是什么类型?怎么操作?
ORACLE中演示如下: CREATE SEQUENCES SQ_ID INCREMENT BY 1 START WITH 1; CREATE OR REPLACE TRIGGER TG_ID_BI_ROW BEFORE INSERT OF ID ON T_TEST FOR EACH ROW STRDATE VARCHAR(10); IID INTEGER; STRID VARCHAR(10) BEGIN STRDATE:=TO_CHAR(SYSDATE,'YYYYMMDD'); SELECT SQ_ID.NEXTVAL INTO IID FROM DUAL; STRID:=LPAD(TO_CHAR(IID),5,'0'); :NEW.ID:=STRDATE||STRID; END; / 如果你的ID是INTEGER的话,最后一句改为 :NEW.ID:=TO_NUMBER(STRDATE||STRID); 我感觉我已经说的够详细了。
事实上,这个ID号应该是一个计算字段
物理字段应该为(T-SQL)
FId int Identity(1,1) not null,
FDate smalldatetime default GetDate() not null,
constraint Pk_Table Primary Key (FDate, FId)
根本不用触发器
FId int not null,
FDate smalldatetime default GetDate() not null
constraint Pk_Table Primary key (FDate, FId)create trigger tr_UpdateTable on TableName
For INSERT
As
declare iNum int
if @@rowcount > 1 return
update inserted
set Fid = (select Fid +1 from TableName where FDate = GetDate())
CREATE SEQUENCES SQ_ID INCREMENT BY 1 START WITH 1;
CREATE OR REPLACE TRIGGER TG_ID_BI_ROW
BEFORE INSERT OF ID ON T_TEST
FOR EACH ROW
STRDATE VARCHAR(10);
IID INTEGER;
STRID VARCHAR(10)
BEGIN
STRDATE:=TO_CHAR(SYSDATE,'YYYYMMDD');
SELECT SQ_ID.NEXTVAL INTO IID FROM DUAL;
STRID:=LPAD(TO_CHAR(IID),5,'0');
:NEW.ID:=STRDATE||STRID;
END;
/
如果你的ID是INTEGER的话,最后一句改为
:NEW.ID:=TO_NUMBER(STRDATE||STRID);
我感觉我已经说的够详细了。