Fire a trigger after a certain interval time

Transact-SQL
1

I have 2 tables 1 is booking and another is vehicle. i have created a trigger on booking table after insert but it is not fired please check bellow details of my table

CREATE TABLE [dbo].[Vehicle_Details](
** [PKF_Vehicle] [bigint] NOT NULL, **
** [Vehicle_Status] varchar NULL,**
** CONSTRAINT [PK_Vehicle_Details] PRIMARY KEY CLUSTERED **
(
** [PKF_Vehicle] ASC**
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Booking](
** [PKF_Booking] [bigint] NOT NULL, **
** [FKP_Vehicle] [bigint] NULL, **
** [Payment_Status] [bit] NULL,**
** [Booking_Date] [datetime] NULL,**
** CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED **
(
** [PKF_Booking] ASC**
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Booking] ADD CONSTRAINT [DF_Booking_Payment_Status] DEFAULT ((0)) FOR [Payment_Status]
GO

ALTER TABLE [dbo].[Booking] ADD CONSTRAINT [DF_Booking_Booking_Date] DEFAULT (getdate()) FOR [Booking_Date]
GO

and my trigger is

create TRIGGER [dbo].[tr_SCHEDULE_Modified_booking]
ON [dbo].[Booking]
AFTER INSERT
AS BEGIN
SET NOCOUNT ON;
declare @status bit;
declare @startdate datetime,@enddate datetime,@currentdate datetime
set @currentdate=GETDATE();

SELECT @enddate=DATEADD(minute, 2, Booking_Date ) from Booking b INNER JOIN Vehicle_Details V
ON B.FKP_Vehicle = V.PKF_Vehicle
and b.Payment_Status=0 and v.Vehicle_Status = 'Not Available'

SELECT @startdate= Booking_Date from Booking b INNER JOIN Vehicle_Details V
ON B.FKP_Vehicle = V.PKF_Vehicle
and b.Payment_Status=0 and v.Vehicle_Status = 'Not Available'

select @status = Payment_Status from dbo.Booking b INNER JOIN Vehicle_Details V
 ON B.FKP_Vehicle =   V.PKF_Vehicle and v.Vehicle_Status = 'Not Available'
  
 if( (@status = 0) and (@currentdate>@enddate)
 )
 Begin
    
BEGIN
    UPDATE Vehicle_Details 
    SET Vehicle_Status = 'Available' 
    FROM Vehicle_Details V INNER JOIN Booking B 
    ON V.PKF_Vehicle = B.FKP_Vehicle  
     and b.Payment_Status=0 and v.Vehicle_Status = 'Not Available' 
END  
END

END

2

Difficult to know where to start with this:

  1. A trigger named Modified_booking should probably be AFTER UPDATE, not AFTER INSERT
  2. No attempt to use either the inserted or deleted pseudo-tables.
  3. No attempt to make set based.
    etc

Please could you explain what you are trying to do?

3

1st I am inserted value in booking table through stored procedure in this proc I am update the vehicle table vehicle _status not available.
Now I am waiting for receiving payment that update the payment status in booking table 1. For payment status I want to wait for only 2 if payment status not change in booking table then I want to fire trigger for changing the vehicle table vehicle _status available.

4

This is something which is not suitable for a table trigger.

The easiest way to do this is to set up a SQL Agent Job which executes every minute and runs something like the following:

UPDATE V
SET Vehicle_Status = 'Available'
FROM dbo.Vehicle_Details V
WHERE V.Vehicle_Status = 'Not Available'
    AND EXISTS
    (
        SELECT 1
        FROM dbo.Booking B
        WHERE B.FKP_Vehicle = V.PKF_Vehicle
            AND B.Payment_Status = 0
            AND CURRENT_TIMESTAMP > DATEADD(minute, 2, B.Booking_Date)
    );