Hello everyone,
I have crated a trigger which is working on table after insert.i want to insert any date of each month for next 5 years.but really i have no idea how to do this.
below the structure of my table and my trigger
ALTER TRIGGER [dbo].[Installment_INSERT]
ON [dbo].[tbl_booked]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AlloteeRegId nvarchar(2500)
DECLARE @plotid int
DECLARE @projectid int
DECLARE @Installment float
DECLARE @insstart int=60
SELECT @AlloteeRegId = INSERTED.AlloteeRegNo FROM INSERTED;
SELECT @plotid=INSERTED.plotid FROM INSERTED;
SELECT @projectid=INSERTED.projectid FROM INSERTED;
SELECT @Installment=inserted.Installment FROM INSERTED;
while(@insstart>0)
Begin
INSERT INTO T_Installment( InstallmentAmount, PayemntDate, plotid, projectid, AllooteeRegNo)
VALUES(@Installment,GETDATE(),@plotid,@projectid,@AlloteeRegId )
set @insstart=@insstart-1
END
END
USE [dbelakshi]
GO
/****** Object: Table [dbo].[T_Installment] Script Date: 11/13/2016 21:44:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T_Installment](
[InstallmentID] [bigint] IDENTITY(1,1) NOT NULL,
[AllooteeRegNo] [nvarchar](250) NULL,
[InstallmentAmount] [float] NULL,
[PaymentStatus] [bit] NULL,
[PayemntDate] [datetime] NULL,
[plotid] [int] NULL,
[projectid] [int] NULL,
[sector] [varchar](50) NULL,
CONSTRAINT [PK_T_Installment] PRIMARY KEY CLUSTERED
(
[InstallmentID] 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].[T_Installment] ADD CONSTRAINT [DF_T_Installment_PaymentStatus] DEFAULT ((0)) FOR [PaymentStatus]
GO
USE [dbelakshi]
GO
/****** Object: Table [dbo].[tbl_booked] Script Date: 11/13/2016 21:45:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_booked](
[AlloteeId] [int] IDENTITY(1,1) NOT NULL,
[AlloteeRegNo] [varchar](250) NULL,
[AlloteeName] [varchar](250) NULL,
[AlloteeGender] [varchar](50) NULL,
[AlloteeFName] [varchar](250) NULL,
[AlloteeMobileNo] [varchar](50) NULL,
[AlloteeEmailId] [varchar](250) NULL,
[AlloteeAddress] [varchar](max) NULL,
[projectid] [int] NULL,
[plotid] [int] NULL,
[BookDate] [date] NULL,
[BookedAmount] [float] NULL,
[PlotTotalCost] [float] NULL,
[Installment] [float] NULL,
[Nominee] [varchar](250) NULL,
[state] [varchar](50) NULL,
[city] [nvarchar](50) NULL,
[pincode] [varchar](50) NULL,
CONSTRAINT [PK_tbl_booked] PRIMARY KEY CLUSTERED
(
[AlloteeId] 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].[tbl_booked] WITH CHECK ADD CONSTRAINT [FK_tbl_booked_tblProjects] FOREIGN KEY([projectid])
REFERENCES [dbo].[tblProjects] ([projectid])
GO
ALTER TABLE [dbo].[tbl_booked] CHECK CONSTRAINT [FK_tbl_booked_tblProjects]
GO