请问用触发器时,进行有条件的更新,触发器里是不是也要加相应的条件语句啊
比如:
CREATE TRIGGER UseMoneyUpdate
ON [PaidRecord]
FOR UPDATE,INSERT
AS
--IF UPDATE (UseMoney)
BEGIN
declare @mypar1 as money
declare @mypar2 as money
declare @mypar3 as money
declare @mypar4 as varchar(25)select @mypar4=Code, @mypar1=UseMoney from inserted select @mypar2 =UseMoney from deletedset @mypar3 = @mypar1 - @mypar2update PaidMoneyRecord set UseMoneyP = UseMoneyP + @mypar3 where code = @mypar4END如果更新PaidRecord时加入时间的条件语句,上面应该怎么改?
比如:
CREATE TRIGGER UseMoneyUpdate
ON [PaidRecord]
FOR UPDATE,INSERT
AS
--IF UPDATE (UseMoney)
BEGIN
declare @mypar1 as money
declare @mypar2 as money
declare @mypar3 as money
declare @mypar4 as varchar(25)select @mypar4=Code, @mypar1=UseMoney from inserted select @mypar2 =UseMoney from deletedset @mypar3 = @mypar1 - @mypar2update PaidMoneyRecord set UseMoneyP = UseMoneyP + @mypar3 where code = @mypar4END如果更新PaidRecord时加入时间的条件语句,上面应该怎么改?
ON [PaidRecord]
FOR UPDATE,INSERT
AS
--IF UPDATE (UseMoney)
if datepart(hour,getdate()) between 8 and 12 --在8点-12点之间触发
BEGIN /*
declare @mypar1 as money
declare @mypar2 as money
declare @mypar3 as money
declare @mypar4 as varchar(25) select @mypar4=Code, @mypar1=UseMoney from inserted select @mypar2 =UseMoney from deleted set @mypar3 = @mypar1 - @mypar2 update PaidMoneyRecord set UseMoneyP = UseMoneyP + @mypar3 where code = @mypar4
*/
update PaidMoneyRecord ---你的语句可以改成下面的
set UseMoneyP = UseMoneyP + i.UseMoney-d.UseMoney
from PaidMoneyRecord p,inserted i,deleted d
where p.code=i.code and i.code=d.codeEND
disable trigger 触发器 ---停用
enable trigger 触发器 --- 启用
update A SET A.UseMoney = A.UseMoney -5 from PaidRecord A
WHERE A.UseDate Between '2008-07-01' AND '2009-07-08'
AND A.UseMoney > 5
AND Exists (
SELECT * FROM PaidMoneyRecord C
WHERE C.Code = A.Code
AND C.UseDate Between '2008-07-01' AND '2009-07-08'
)
那触发器应该怎么改?