September 22, 2016 at 3:53 am
I want through this trigger if payment_status in booking table not update within 2 mins then in vehicle table Vehicle_Status has been changed. but trigger not fired after inserted
CREATE TABLE [dbo].[Vehicle_Details](
** [PKF_Vehicle] [bigint] NOT NULL, **
** [Vehicle_Status] [varchar](50) 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
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply