--try
CREATE TRIGGER [TR_StatusModify] ON [dbo].[AS_Main]
FOR UPDATE
AS Declare @EndDate Datetime
Declare @ID varchar(20) Select @EndDate =Max(AS_EndDate),@ID=AS_PositionID From AS_Main group by AS_PositionID
If @EndDate <= getdate()
Update AS_Position set AS_Status = 2 Where ID =@ID
CREATE TRIGGER [TR_StatusModify] ON [dbo].[AS_Main]
FOR UPDATE
AS Declare @EndDate Datetime
Declare @ID varchar(20) Select @EndDate =Max(AS_EndDate),@ID=AS_PositionID From AS_Main group by AS_PositionID
If @EndDate <= getdate()
Update AS_Position set AS_Status = 2 Where ID =@ID
FOR UPDATE
AS Declare @EndDate Datetime
Declare @ID varchar(20) Select @EndDate =Max(AS_EndDate),@ID=AS_PositionID From inserted group by AS_PositionID
If @EndDate <= getdate()
Update AS_Position set AS_Status = 2 Where ID =@ID