View VS Stored Procedure/Trigger

[jgurgen]

I have a view that i created with calculations for interest, balance and dates. Im using a total of 4 views to do all these calculations. I had to use 4 because some calculations are based of a previous calc, for example, I have to do a DateDiff on 2 fields in 1 view then in the next view i use the Difference between dates and calc interest on the number of days.

In this sistuation would this be better handled using Triggers and Stored Procedures to do all this, or is the views the most efficient way to do this? I would like to have the values stored in the database, so i was thinking of running a stored procedure to copy the data into an acutal table.

 

[skuhlman]

I would do this using a trigger in most cases. The exception would be a table with a very large number of records, where changes to many records at a time happens on a regular basis. If changes to few records are being processed at any given time, I like the trigger as a way of keeping the "summary fields" in synch. Another thing to concider is if the file is being accessed by interactive users and what would be the effect of locking the table for extended periods of time.

 

[jgurgen]

The problem is is that to calculate the datediff for daysopen its based off the transaction date. So 1 transaction has a start date initially and the end date is generated when another transaction is inserted. But not all transactions are going to be included and the user has a choice on which ones so if there is a change to the flag then i have to recalculate all transactions because the end dates are now going to be different on all of them. It just seems like way too many triggers to write to capture all possible changes

 

[skuhlman]

The following code creates a table (LoanTest), puts 3 sample starting records into the table, creates the indexes for the table, and creates an insert trigger for the table. In this example the insert trigger will use the startdate on the inserted record as the end date for any un-ended loan of the same type for the same person which has no end date. It probably doesn't make much sense, but it is strictly an example of how the trigger could be set up.

This trigger uses a CURSOR which is not always a good thing in SQL. It is not recommended especially when many, many records are being updated at once. SQL experts prefer updating thousands of records at a time instead of a single record at a time as is done inside this cursor. But, based on what you've written, it sounds like the records are being processed in more of an online transactional system than by massive back-end data loads. In any case, this cursor would work, it just wouldn't be as efficient. The advantage to the cursor is that if multiple records are inserted at the same time which could be dependant upon each other (two records with the same account and same loan type in this example) they will be processed sequentially.

I wrote only one trigger for INSERT. I would probably write three triggers (seperate ones for INSERT, UPDATE, and DELETE) if I were doing it myself, but they could all be combined into the same trigger with additional coding.

Here's some sample code:

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