看了如下代码应该明白我什么意思。。该怎么改呢
CREATE trigger TransportRecordsInsertTrg
on TransportRecords_FID_1
for insert
AS
declare @MaxTemperature float
declare @MinTemperature float
select @MaxTemperature=m.MaxTemp,@MinTemperature=m.MinTemp from
MonitorRules m inner join inserted i
on m.BarCode=i.BarCode
if i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature
begin
insert into MonitorLog (BarCode,RID,TemMonitor) Values(m.BarCode,m.RID,i.Temperature)
end RETURN
CREATE trigger TransportRecordsInsertTrg
on TransportRecords_FID_1
for insert
AS
declare @MaxTemperature float
declare @MinTemperature float
select @MaxTemperature=m.MaxTemp,@MinTemperature=m.MinTemp from
MonitorRules m inner join inserted i
on m.BarCode=i.BarCode
if i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature
begin
insert into MonitorLog (BarCode,RID,TemMonitor) Values(m.BarCode,m.RID,i.Temperature)
end RETURN
--試試以下:
CREATE trigger TransportRecordsInsertTrg
on TransportRecords_FID_1
for insert
AS
begin
if EXISTS
(
select *
FROM MonitorRules m
inner join inserted i on m.BarCode=i.BarCode
WHERE i.Temperature >m.MaxTemp or i.Temperature <m.MinTemp
)
begin
insert into MonitorLog (BarCode,RID,TemMonitor)
select m.BarCode,m.RID,i.Temperature
FROM MonitorRules m
inner join inserted i on m.BarCode=i.BarCode
WHERE i.Temperature >m.MaxTemp or i.Temperature <m.MinTemp
end
END
试试这样呢:
CREATE trigger TransportRecordsInsertTrg
on TransportRecords_FID_1
for insert
AS
declare @MaxTemperature float
declare @MinTemperature float
select @MaxTemperature=m.MaxTemp,@MinTemperature=m.MinTemp from
MonitorRules m inner join inserted i
on m.BarCode=i.BarCode
insert into MonitorLog (BarCode,RID,TemMonitor)
select m.BarCode,m.RID,i.Temperature
from MonitorRules m
inner join inserted i
on m.BarCode=i.BarCode
where i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature
go
试试这样呢:
CREATE trigger TransportRecordsInsertTrg
on TransportRecords_FID_1
for insert
AS
declare @MaxTemperature float
declare @MinTemperature float
select @MaxTemperature=m.MaxTemp,@MinTemperature=m.MinTemp from
MonitorRules m inner join inserted i
on m.BarCode=i.BarCode
insert into MonitorLog (BarCode,RID,TemMonitor)
select m.BarCode,m.RID,i.Temperature
from MonitorRules m
inner join inserted i
on m.BarCode=i.BarCode
where i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature
go
如果这样写的话,那每次都会向MonitorLog插入一条记录吧,我现在只想当 i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature 时插入记录,3L的可以,不过用了两次同样的inner join 是不是可以简化下,请高手指点一二
试试这样呢:
CREATE trigger TransportRecordsInsertTrg
on TransportRecords_FID_1
for insert
AS
declare @MaxTemperature float
declare @MinTemperature float
select @MaxTemperature=m.MaxTemp,@MinTemperature=m.MinTemp from
MonitorRules m inner join inserted i
on m.BarCode=i.BarCode
insert into MonitorLog (BarCode,RID,TemMonitor)
select m.BarCode,m.RID,i.Temperature
from MonitorRules m
inner join inserted i
on m.BarCode=i.BarCode
where i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature
go
如果这样写的话,那每次都会向MonitorLog插入一条记录吧,我现在只想当 i.Temperature >@MaxTemperature or i.Temperature <@MinTemperature 时插入记录,3L的可以,不过用了两次同样的inner join 是不是可以简化下,请高手指点一二后面有一个where条件,之后符合条件后,才会插入,所以不是每一次,都一定会插入一条记录的,有可能不插入记录。