Trigger seeking time period

silentmars

Brother, I write a program. I have such a need. I think it should be implemented using triggers.

Use Table 1 to store the moment of each swipe, and Table 2 to store the length of time between swipes.

Structure of Table 1:
Field name type
SWITCH String
CALTIME Datetime

Structure of Table 2:
Field name type
TIMESPAN Decimal

That is,
When the card swipe is recorded as 'On' 2017-01-04 20:00:00, the trigger does nothing;
When the swipe record is 'Off' 2017-01-04 21:30:00, the trigger is based on this pair of data,
Add a record to Table 2 and record a length of 1.5 hours

michealhenry

create trigger t_time on Table 1
for insert
as
begin
insert into Table 2 (TIMESPAN)
select datediff (hour, a.CALTIME, b.CALTIME)-if you need to record the date in Table 2, you can add a column here convert (varchar (10), a.CALTIME, 120)
from Table 1 a inner join inserted b
      on a.SWITCH = 'On' and b.SWITCH = 'Off'
         and convert (varchar (10), a.CALTIME, 120) = convert (varchar (10), b.CALTIME, 120)
end
go

wgfxman

create trigger t_time on Table 1
for insert
as
begin
insert into Table 2 (TIMESPAN)
select datediff (hour, (select MAX (CALTIME) from Table 1 WHERE SWITCH = 'On'), inserted.CALTIME)
WHERE inserted.SWITCH = 'Off'
end
go

waxiaoning

create trigger trgtest on tablename1
instead of insert
as
begin
insert into tablename2 (timespan)
select datediff (hour, b.caltime, a.caltime)
from inserted a,
(select max (caltime) as caltime from tablename1) b
where a.switch = 'On'
insert into tablename1 select * from inserted
end

waxiaoning

The premise is that only one record is inserted at a time

hiqwolfzzh

With after trigger, can we achieve the effect of opportunism, useless Inserted table.

create trigger t_time on Table 1
after insert
as
begin
declare @switch char (5)
declare @mtime datetime
select @ switch = switch from Table 1 where CALTIME = (select Max (CALTIME) from Table 1)
if @ switch = 'OFF'
insert into Table 2 (timespan)
select datediff (hour, caltime, caltime)
from Table 1
end
go