有个表TBC(id,state,endtime),分别为varchar,int(只限1,2,3三个数),datetime格式,id为主键
请大家帮忙写一个触发器,
在插入或者更新的情况下,
如果state=2或者3时,自动设endtime为当前时间,
如果当state=1的时候,直接插入或更新。
请大家帮忙写一个触发器,
在插入或者更新的情况下,
如果state=2或者3时,自动设endtime为当前时间,
如果当state=1的时候,直接插入或更新。
as
update tbc set endtime=getdate() from tbc a,inserted b where a.id=b.id and b.state in (2,3)
go
as
if(select state from inserted)<>1
update a set endtime=getdate()
from TBC a, inserted b where a.id=b.id
for insert,update
as
begin
DECLARE @STATE INT
IF EXISTS(SELECT 1 FROM INSERTED WHERE COUNT(*) > 0)
BEGIN
SELECT @STATE = LO.STATE FROM (SELECT TOP 1 * FROM INSERTED) LO
IF (@STATE = 1)
BEGIN
INSERT tbc ([ID], [STATE],ENDTIME)
SELECT [ID], [STATE],ENDTIME FROM INSERTED
END
ELSE IF (@STATE = 2 OR @STATE = 3)
BEGIN
INSERT tbc ([ID], [STATE],ENDTIME)
SELECT [ID], [STATE],GETDATE() FROM INSERTED
END
END
ELSE if EXISTS(select 1 From UPDATED where COUNT(*) > 0 )
BEGIN
SELECT @STATE = LO.STATE FROM (SELECT TOP 1 * FROM UPDATED) LO
IF (@STATE = 1)
BEGIN
UPDATE TBC SET STATE = S.STATE , ENDTIME= S.ENDTIME
FROM TBC T,UPDATED S
WHERE T.ID = S.ID
END
ELSE IF (@STATE = 2 OR @STATE = 3)
BEGIN
UPATE TBC SET STATE =S.STATE, ENDTIME=GETDATE()
FROM TBC T,UPDATED S
WHERE T.ID = S.ID
END
END
end
if exists (select name from sysobjects
where name='tbc_trig' and type='TR')
drop trigger tbc_trig
go
create trigger tbc_trig
on tbc
for insert,update
as
if(select state from inserted)in (2,3)
update a set endtime=getdate()
from tbc a, inserted b where a.id=b.id go
for update,insert
begin
declare @id varchar(10)
declare @state int
select @id=id,@state=state from insertedif @state=2 or @state=3
begin
update table_name set endtime=getdate() where id = @id and state = @state
end end