请教一个不是很难的触发器写法
求一个更新触发器
表tab中有字段status,id
当某记录更新时status变成1时，把该记录的id改成当前日期＋编号
如当前只有一条记录则改成当前日期＋起始编号如0708230001
如当前表中已经有其他记录时，id改成当前日期＋(最大编号+1),最大编号为id是本日期的编号最大值，如当前日期编号最大的记录为0708230221，则现记录id为0708230222
请教这样的一个update触发器
[解决办法]
---例子---
create trigger t_update_id on tab
for update
as
begin
update tab
set id=replace(convert(char(10),getdate(),120), '- ', ' ')+rtrim(cast(right((select max(id) from tab),4) as int)+1)
where status=1
end
[解决办法]
-- =============================================
-- Create basic Instead Of Trigger
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N 'TSER '
AND type = 'TR ')
DROP TRIGGER TSER
GO
CREATE TRIGGER TSER
ON SER_DEFINE
FOR INSERT
NOT FOR REPLICATION
AS
BEGIN
DECLARE @ID INT,
@strsql varchar(8000)
SELECT @ID=ID FROM INSERTED
select @strsql= ' update tab set id=[函数就你自己写吧,反正getdate（）、max（）VALUE(),RIGHT()几个的组合] where id= '+@id
exec @strsql
END
GO
[解决办法]
create table tb(colID int identity,status int,ID nvarchar(14))
go
create trigger test on tb
for insert,update
as
begin
if update(status)
begin
declare @status int,@colID int
select @status = status,@colID = colID from inserted
if @status = 1
begin
declare @Nowdate nvarchar(8),@OtherDate nvarchar(8),@Num nchar(13),@ID int
select @Nowdate = convert(char(8),getdate(),112),@OtherDate = '19990101 ',@ID = 0,@Num = ' '
select @OtherDate = substring(ID,1,8),@ID = right(ID,4) from tb where substring(ID,1,8) = @Nowdate
if @OtherDate <> @Nowdate
begin
select @ID = 0
end;
select @Num = @Nowdate + right((10000 + @ID + 1),4)
update tb set id = @Num where colID = @colID
end;
end;
end;
go
insert into tb(status) select 1
select * from tb
[解决办法]
---例子---
create table tab(status int,id varchar(20))
insert tab
select 0,null
go
create trigger t_update_id on tab
for update
as
begin
update tab
set id=replace(convert(char(10),getdate(),120), '- ', ' ')+right( '0000 '+rtrim(cast(right((select isnull(max(id),0) from tab),4) as int)+1),4)
where status=1
end
go
select * from tab
update tab set status=1
select * from tab
drop table tab
/*
status id
----------- --------------------
1 200708230002
(1 row(s) affected)
*/
[解决办法]
create trigger tr_tb1_u on tb1 for update
as
if update(status)
begin
declare @maxid varchar(13)
set @maxid = ISNULL((select max(id) from tb1),cast(year(getdate())as varchar(4)) + cast(month(getdate())as varchar(2)) +cast(day(getdate())as varchar(2)) + '001 ')
update tb1 set id = cast(year(getdate())as varchar(4)) + cast(month(getdate())as varchar(2)) +cast(day(getdate())as varchar(2)) + rigth( '000 ' +cast(cast(right(@maxid,3) as int)+1)as varchar(3) ,3) from tb1,inserted where inserted.id = tb1.id and inserted.status = 1
end
