CREATE TRIGGER trig1
on account
FOR DELETE
AS
update rep
set
day = (case when (a.rq = b.rq) then (day - b.day) else day end),
month = month - b.month
year = year - b.year
FROM rep a INNER JOIN deleted b
ON a.rq >= b.rq
where b.name = a.name or a.name = '全部'
GO
on account
FOR DELETE
AS
update rep
set
day = (case when (a.rq = b.rq) then (day - b.day) else day end),
month = month - b.month
year = year - b.year
FROM rep a INNER JOIN deleted b
ON a.rq >= b.rq
where b.name = a.name or a.name = '全部'
GO
--------------------------------------------------------------------------------
CREATE TRIGGER trig1
on account
FOR DELETE
AS
update rep
set
day = (case when ( a.rq = b.rq or a.name = '全部' )
then (day - b.day)
else day end) ,
month = (case when ( convert(char(6),a.rq,112) = convert(char(6),b.rq,112) )
then (month - b.month)
else month end) ,
year = (case when ( year(a.year) = year(b.year) )
then year - b.year
else year end)
FROM
rep a
INNER JOIN
deleted b
ON
a.rq >= b.rq
where
b.name = a.name or a.name = '全部'
GO
请再帮忙谢谢。
ON account
FOR DELETE
AS
update rep
set
day = (case when ( a.rq = b.rq or a.name = '全部' )
then (day - b.je)
else day end) ,
month = (case when ( convert(char(6),a.rq,112) = convert(char(6),b.rq,112) )
then (month - b.je)
else month end) ,
year = (case when ( year(a.year) = year(b.year) )
then year - b.je
else year end)
FROM
rep a
INNER JOIN
deleted b
ON
a.rq >= b.rq
where
b.name = a.name or a.name = '全部'
GO
表中并不存在month,year这些字段,只有rq (日期)这个字段。
所以year = (case when ( year(a.year) = year(b.year) )
通不过啊。
请再帮忙谢谢。
ON account
FOR DELETE
AS
update rep
set
day = (case when ( a.rq = b.rq or a.name = '全部' )
then (day - b.je)
else day end) ,
month = (case when ( convert(char(6),a.rq,112) = convert(char(6),b.rq,112) )
then (month - b.je)
else month end) ,
year = (case when ( year(a.rq) = year(b.rq) )
then year - b.je
else year end)
FROM
rep a
INNER JOIN
deleted b
ON
a.rq >= b.rq
where
b.name = a.name or a.name = '全部'
GO
ON account
FOR DELETE
AS
update rep
set
day = (case when ( a.rq = b.rq or (a.name = '全部' and a.rq <= b.rq) )
then (day - b.je)
else day end) ,
month = (case when ( convert(char(6),a.rq,112) = convert(char(6),b.rq,112) )
then (month - b.je)
else month end) ,
year = (case when ( year(a.rq) = year(b.rq) )
then year - b.je
else year end)
FROM
rep a
INNER JOIN
deleted b
ON
a.rq >= b.rq
where
b.name = a.name or a.name = '全部'
GO