Trigger, Query or Function? (Insert Into)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger, Query or Function? (Insert Into)

Author

Topic

e.bar
Starting Member

25 Posts

Posted - 2003-03-20 : 13:51:03

I have two tables, Loan and MonthlyPayments.

When I insert values on Loan fields, I need an automatic insertion
of rows in MonthlyPayments table, like this:

Loan_table
LoanID = Generated at insertion
Period = 02/2003
Total = $ 1000.00
Payments = 5

MonthlyPayments_table (rows inserted automatically)
LoanID| Payments | Months
"" | $ 200.00 | 03/2003
"" | $ 200.00 | 04/2003
"" | $ 200.00 | 05/2003
"" | $ 200.00 | 06/2003
"" | $ 200.00 | 07/2003

PS1: MontlhyPayments table has a primary key
PS2: MonthlyPayments_LoanID have duplicate values.
PS3: There is a relationship with these two tables (Loan.LoanID = MonthlyPayments.LoanID.
PS4: Loan.Period and MontlyPayments.Months is based on datatime format.

Tks.

Edited by - merkin on 03/20/2003 16:13:17

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-20 : 15:20:10

I cheated and made the Period and Month Field an actual datetime column.

CREATE TRIGGER Loan_Table_INSTr ON Loan_Table
FOR INSERT
AS

If Exists (Select 1 From Inserted) And Not Exists (Select 1 From Deleted)
BEGIN
Declare @Loop int, @LoanId int, @Period datetime, @Total decimal(15,2), @Payments int
Select @LoanID = LoadId, @Period = Period, @Total = Total, @Payments = Payements, @Loop = 1
While @Loop < @Payments
BEGIN
INSERT INTO MonthlyPayments_table (LoadId, Payments, Months)
SELECT @LoanId, (@Total/@Payments), DateAdd(mm,@Loop,@Period)
SELECT @Loop = @Loop + 1
END

END

Go

Brett

8-)

Edited by - x002548 on 03/20/2003 15:21:34

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-03-20 : 17:13:48

Brett,

Why the existence check for the DELETED table on an INSERT trigger?
And why the existence check for the INSERTED table when a simple @@RowCount test will do the same thing? (I know that is nitpicking...)

But this is the way I would do it...

First create a Numbers table listing the numbers from 1 to n, where n is the maximum number of Payments, you should have a CHECK constraint on the Payments column that enforces this rule.


Create table Numbers (Number int Primary Key CHECK(Number > 0))
go
Insert Numbers (Number)
	Select 1 as Number
	union all
	Select 2
	union all
	Select 3
	union all
	Select 4
	union all
	Select 5
GO

After that the trigger is easy and able to cope with a multi-row insert...


CREATE TRIGGER trLoan_Insert
ON Loan
FOR INSERT
AS
IF @@ROWCOUNT > 0
BEGIN
INSERT Loan_Payment (LoanID, Payment, PayDate)
SELECT LoanID, 1.00* Total/Payments , DATEADD(mm,Number, Period) 
FROM Inserted CROSS JOIN Numbers N
WHERE Number <= Payments
END

And e.bar.. I am sure you could find a Natural Key in you MonthlyPayment table... Perhaps LoanID and Months...

DavidM

"SQL-3 is an abomination.."

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-21 : 15:20:31

I do it that way I guess, because when I have a single trigger handle inserts, updates and deletes you need to know what happened.

I guess it's redundant if you create a single trigger for each action.

I s there a benefit to have 1 trigger over three?

I don't know.

I guess I thought it was easier to maintain. Am I taking a performace hit?

I don't know (you'll notice I say that a lot).

I haven't see an impact.

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-22 : 00:00:17

As long as you're aware of the ramifications, then keeping them all together in one trigger isn't too bad. I like to use separate triggers for each op so that I know that only the code that's supposed to run during an INSERT actually runs. It's really embarrassing when a slip in logic accidentally DELETEs all the rows that were just inserted!

"Damn! That's the tenth time I've imported that table, and the shit just won't go in!!! Oooooops..."

Yep, I've said that more than once.

Edited by - robvolk on 03/22/2003 00:00:57

e.bar
Starting Member

25 Posts

Posted - 2003-03-22 : 09:20:57

Hey guys, thank you for help me! All replies came as a sunny day and I got what I need. robvolk, your picture is great!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-22 : 09:43:23

Wow, that's the first time anyone has said that about THAT picture! Come to think of it, I think that's the first time anyone said that about ANY of my pictures...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources