create trigger tr_insert on tabA for insert --1.当tabA表插入记录时,触发器启动 as -- 2.当inserted记录里的tdatetime是当天20点的话,就把记录插入到tabB表里 insert tabB select * from inserted where datepart(hour, tdatetime)=20-- 3.如果inserted记录里的tdatetime是19点或19点之前的,就用maxB与前一天记录的maxA比较,如果maxB>maxA就把maxA=maxB.(minA也是如此) update b set maxA = case when i.maxB > b.maxA then i.maxB else b.maxB end, minA = case when i.minB > b.minA then i.minB else b.minB end from inserted i, tableB b where datepart(hour, i.tdatetime) <= 19 and datediff(day, b.tdatetime, i.tdatetime) = 1 -- 4.如果inserted记录里的tdatetime是21点或21点之后的,就用maxB与当天记录的maxA比较,如果maxB>maxA就把maxA=maxB.(minA也是如此) update b set maxA = case when i.maxB > b.maxA then i.maxB else b.maxB end, minA = case when i.minB > b.minA then i.minB else b.minB end from inserted i, tableB b where datepart(hour, i.tdatetime) >= 21 and datediff(day, b.tdatetime, i.tdatetime) = 1
for insert --1.当tabA表插入记录时,触发器启动
as
-- 2.当inserted记录里的tdatetime是当天20点的话,就把记录插入到tabB表里
insert tabB select * from inserted where datepart(hour, tdatetime)=20-- 3.如果inserted记录里的tdatetime是19点或19点之前的,就用maxB与前一天记录的maxA比较,如果maxB>maxA就把maxA=maxB.(minA也是如此)
update b set
maxA = case when i.maxB > b.maxA then i.maxB else b.maxB end,
minA = case when i.minB > b.minA then i.minB else b.minB end
from inserted i, tableB b
where datepart(hour, i.tdatetime) <= 19
and datediff(day, b.tdatetime, i.tdatetime) = 1
-- 4.如果inserted记录里的tdatetime是21点或21点之后的,就用maxB与当天记录的maxA比较,如果maxB>maxA就把maxA=maxB.(minA也是如此)
update b set
maxA = case when i.maxB > b.maxA then i.maxB else b.maxB end,
minA = case when i.minB > b.minA then i.minB else b.minB end
from inserted i, tableB b
where datepart(hour, i.tdatetime) >= 21
and datediff(day, b.tdatetime, i.tdatetime) = 1