USE [CDM]
GO
/****** Object: Trigger [dbo].[UpdateAkisFlight] Script Date: 2018/6/14 16:43:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[UpdateAkisFlight]
ON [dbo].[FlightWarnTime]
FOR INSERT,DELETE
AS
BEGIN
if(exists(select 1 from inserted) and not exists(select 1 from deleted))
begin
declare @TwoCode nvarchar(50),@FlightNum nvarchar(50),@ThreeCode nvarchar(50),@Etd datetime,@FlightId int,@Calsign nvarchar(50),@ID int
select @TwoCode=TwoCode,@FlightNum=FlightNumber,@Etd = Etd,@ID = Id from inserted
select @ThreeCode=Code3 from Company with(nolock) where Code2=@TwoCode
if @ThreeCode is not null
begin
set @Calsign=@ThreeCode+@FlightNum
if @FlightNum is not null
begin
update [dbo].[FlightWarnTime] set Calsign=@Calsign where Id=@ID
end
if @Etd is not null
begin
select @FlightId=FlightId from [AKIS].[dbo].[ValidFlight] with(nolock) where Calsign=@Calsign and ABS(DATEDIFF(MINUTE,Etd,@Etd)) <=60 and DepAddrName='ZHCC'
end
if @FlightId is not null
begin
update [dbo].[FlightWarnTime] set AkisFlightId=@FlightId where Id=@ID
end
end
end
END