if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tr_LoanTest]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tr_LoanTest]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LoanTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[LoanTest]
GO
CREATE TABLE [dbo].[LoanTest] (
[MyKeyField] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[AcctNo] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoanType] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[LoanAmt] [money] NULL ,
[Periods] [int] NULL ,
[Rate] [numeric](18, 4) NULL ,
[Payment] [money] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LoanTest] WITH NOCHECK ADD
CONSTRAINT [PK_LoanTest] PRIMARY KEY CLUSTERED
(
[MyKeyField]
) ON [PRIMARY]
GO
CREATE INDEX [idx_AcctNo] ON [dbo].[LoanTest]([AcctNo]) ON [PRIMARY]
GO
CREATE INDEX [idx_LoanType] ON [dbo].[LoanTest]([LoanType]) ON [PRIMARY]
GO
insert into LoanTest (AcctNo, LoanType, StartDate, EndDate, LoanAmt, Periods, Rate, Payment)
values ('1111111111','A','2000-01-01',null,165000.00,180,.0725,0)
insert into LoanTest (AcctNo, LoanType, StartDate, EndDate, LoanAmt, Periods, Rate, Payment)
values ('1111111111','B','2005-12-27',null,1540.00,60,.0275,0)
insert into LoanTest (AcctNo, LoanType, StartDate, EndDate, LoanAmt, Periods, Rate, Payment)
values ('2222222222','A','1989-07-15',null,13100.00,360,.1025,0)
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER tr_LoanTest ON dbo.LoanTest
FOR INSERT
AS
declare @MyKeyField Integer
declare @AcctNo char(10)
declare @LoanType char(1)
declare @StartDate datetime
declare @EndDate datetime
declare @LaonAmt money
declare @Periods integer
declare @Rate Numeric(9,4)
declare @Payment money
declare loantest_cursor cursor for
select MyKeyField, AcctNo, LoanType, StartDate, EndDate
from inserted
open loantest_cursor
fetch next from loantest_cursor
into @MyKeyField, @AcctNo, @LoanType, @StartDate, @EndDate
while @@Fetch_Status = 0
begin
-- make sure there's a vaid start date on the current record
if @StartDate is null
begin
set @StartDate = getdate()
update LoanTest
set StartDate = @StartDate
where MyKeyField = @MyKeyField
end
-- update the END DATE on the old record
update LoanTest
set EndDate = isnull(@StartDate,getdate())
where AcctNo = @AcctNo
and LoanType = @LoanType
and EndDate is null
and MyKeyField <> @MyKeyField
fetch next from loantest_cursor
into @MyKeyField, @AcctNo, @LoanType, @StartDate, @EndDate
end
close loantest_cursor
deallocate loantest_cursor
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO