SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help w/ Stored Procedure Please!!
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 3

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/08/2008 :  22:14:12  Show Profile  Reply with Quote
Hi,

Here are 2 of my tables:

Videos
VideoID
MovieID
Price
FormatID
VideoStatusID


RentalRecordRentalID
MemberID
VideoID
Total
DateRented
DateDue
DateReturned


I'm trying to write a SP to insert a record into the table RentalRecord. I want to get Price from the Videos table to calculate my Total in the RentalRecord table. I don't think I am doing it right as I am getting an error about, "Cannot insert the value NULL into column 'Total', table 'annawebappdb.dbo.RentalRecord'; column does not allow nulls. INSERT fails."

Here's my SP and any help is greatly appreciated! Thanks




create proc [dbo].[sp_AddRentalRecord]
/* input parameters */
(@MemberID int,
@VideoID int)
as
begin

begin transaction

--------

declare @Total money

set @Total = (select Price*1.05 from Videos where VideoID = @VideoID)

------------- */

insert into RentalRecord
values (@MemberID,@VideoID,@Total,GETDATE(), DATEADD(dd,5,GETDATE()),null)

-- transaction error handling; if error number is not zero then there's a failure, rollback transaction, and print message

if @@error <> 0
begin

rollback transaction

select 'Insert Failed'

return

end

else

begin

select 'Rental Record Added Successfully'

end
commit transaction

end

malaytech2008
Yak Posting Veteran

India
95 Posts

Posted - 11/09/2008 :  00:35:46  Show Profile  Reply with Quote
you make RentalID as Identity column.

malay
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  00:40:58  Show Profile  Reply with Quote
it seems like you dont have a record in Videos table for the passed value of @VideoID. that causes @Toatl to be null and since Total is defined as a NOT NULL column, it causes an error when you try to insert value of @Total to it which is NULL.
First try this and see if you've a record existing for VideoID

select Price*1.05 from Videos where VideoID = @VideoID
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  10:52:45  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

it seems like you dont have a record in Videos table for the passed value of @VideoID. that causes @Toatl to be null and since Total is defined as a NOT NULL column, it causes an error when you try to insert value of @Total to it which is NULL.
First try this and see if you've a record existing for VideoID

select Price*1.05 from Videos where VideoID = @VideoID




Thanks, you were right. I didn't have a record in the Videos table.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  11:37:29  Show Profile  Reply with Quote
In such cases first insert a record to Video table, get the generated VideoID and then use it along with Total to insert to RentalRecord table.
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  12:21:04  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

In such cases first insert a record to Video table, get the generated VideoID and then use it along with Total to insert to RentalRecord table.



OK thanks. I have another question. I created a trigger to update the Balance in the Customers table after inserting a record in the Payment table, but it didn't work as I got an error.

Customers
MemberID
Lname
.
.
.
Balance


Payment
PaymentID
MemberID
AmountPaid
Date


Did I do something wrong in my trigger? Thanks




create trigger trg_u_customer_balance
on Payment
for insert
as
begin
--local variable
declare @MemberID int, @AmountPaid money

--assign value to local variable
select @AmountPaid = (select AmountPaid from inserted)
select @MemberID = (select MemberID from inserted)

--update account balance when payment is made
update Customers
set Balance = (Balance - @AmountPaid)
where MemberID = @MemberID

--transaction error handling: if the error is not zero, then there's a failure, rollback transaction, and print message
if @@error != 0 or @@rowcount != 1
begin
rollback transaction
select 'Balance Not Updated'
return
end

end
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  12:23:05  Show Profile  Reply with Quote
quote:
Originally posted by sas0riza

quote:
Originally posted by visakh16

In such cases first insert a record to Video table, get the generated VideoID and then use it along with Total to insert to RentalRecord table.



OK thanks. I have another question. I created a trigger to update the Balance in the Customers table after inserting a record in the Payment table, but it didn't work as I got an error.

Customers
MemberID
Lname
.
.
.
Balance


Payment
PaymentID
MemberID
AmountPaid
Date


Did I do something wrong in my trigger? Thanks




create trigger trg_u_customer_balance
on Payment
for insert
as
begin
--local variable
declare @MemberID int, @AmountPaid money

--assign value to local variable
select @AmountPaid = (select AmountPaid from inserted)
select @MemberID = (select MemberID from inserted)

--update account balance when payment is made
update Customers
set Balance = (Balance - @AmountPaid)
where MemberID = @MemberID

--transaction error handling: if the error is not zero, then there's a failure, rollback transaction, and print message
if @@error != 0 or @@rowcount != 1
begin
rollback transaction
select 'Balance Not Updated'
return
end

end




Sorry, I forgot to include the error:


(0 row(s) affected)

(1 row(s) affected)
Msg 3609, Level 16, State 1, Procedure sp_AddPayment, Line 13
The transaction ended in the trigger. The batch has been aborted.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  12:25:21  Show Profile  Reply with Quote
try like below

create trigger trg_u_customer_balance
on Payment
for insert
as
begin

--update account balance when payment is made
update c.Customers
set c.Balance = c.Balance - i.AmountPaid
from Customers c
inner join inserted i
on c.MemberID = i.MemberID

--transaction error handling: if the error is not zero, then there's a failure, rollback transaction, and print message
if @@error != 0 or @@rowcount =0
begin
rollback transaction
select 'Balance Not Updated'
return
end

end
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  12:31:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

try like below

create trigger trg_u_customer_balance
on Payment
for insert
as
begin

--update account balance when payment is made
update c.Customers
set c.Balance = c.Balance - i.AmountPaid
from Customers c
inner join inserted i
on c.MemberID = i.MemberID

--transaction error handling: if the error is not zero, then there's a failure, rollback transaction, and print message
if @@error != 0 or @@rowcount =0
begin
rollback transaction
select 'Balance Not Updated'
return
end

end




Hmm, I get invalid object name?

Msg 208, Level 16, State 1, Procedure trg_u_customer_balance, Line 10
Invalid object name 'c.Customers'.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  12:33:36  Show Profile  Reply with Quote
ah...copy paste mistake

create trigger trg_u_customer_balance
on Payment
for insert
as
begin

--update account balance when payment is made
update c
set c.Balance = c.Balance - i.AmountPaid
from Customers c
inner join inserted i
on c.MemberID = i.MemberID

--transaction error handling: if the error is not zero, then there's a failure, rollback transaction, and print message
if @@error != 0 or @@rowcount =0
begin
rollback transaction
select 'Balance Not Updated'
return
end

end
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  12:36:56  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

ah...copy paste mistake

create trigger trg_u_customer_balance
on Payment
for insert
as
begin

--update account balance when payment is made
update c
set c.Balance = c.Balance - i.AmountPaid
from Customers c
inner join inserted i
on c.MemberID = i.MemberID

--transaction error handling: if the error is not zero, then there's a failure, rollback transaction, and print message
if @@error != 0 or @@rowcount =0
begin
rollback transaction
select 'Balance Not Updated'
return
end

end





Still same thing:
(0 row(s) affected)

(1 row(s) affected)
Msg 3609, Level 16, State 1, Procedure sp_AddPayment, Line 13
The transaction ended in the trigger. The batch has been aborted.


The balance didn't update (trigger didn't work) and the record didn't get inserted into the Payment table when I execute the SP.

Here is my SP for the Payment table:

create proc sp_AddPayment
/* input parameters */
(@MemberID int,
@AmountPaid money)

as
begin



begin transaction
insert into Payment
values (@MemberID,@AmountPaid,GETDATE())

-- transaction error handling; if error number is not zero then there's a failure, rollback transaction, and print message
if @@error <> 0
begin
rollback transaction
select 'Insert Failed'
return
end
else
begin
select 'Payment Added Successfully'
end

commit transaction

end


Thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  12:41:17  Show Profile  Reply with Quote
what are conditions under which you want to rollback transaction inside trigger?
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  12:44:41  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

what are conditions under which you want to rollback transaction inside trigger?



I don't quite understand your question. Can you please give me more details? Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  12:48:16  Show Profile  Reply with Quote
which all condition you should be rollbacking the transaction in trigger? your initially given condition was if @@error != 0 or @@rowcount != 1
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  12:50:54  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

which all condition you should be rollbacking the transaction in trigger? your initially given condition was if @@error != 0 or @@rowcount != 1



Oh I guess if the MemberID doesn't exist...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  12:54:52  Show Profile  Reply with Quote
so in this case were memberid you passed already existing? the error message you got simply tells that it has rollbacked transaction inside trigger and so it wont be executing rest of statements following insert in the SP. this can be either due to any error (@@error >0) or due to no memberid record in customer table(@@rowcount=0)
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  13:02:30  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

so in this case were memberid you passed already existing? the error message you got simply tells that it has rollbacked transaction inside trigger and so it wont be executing rest of statements following insert in the SP. this can be either due to any error (@@error >0) or due to no memberid record in customer table(@@rowcount=0)



You are right again! I was using a MemberID that didn't exist.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  13:05:09  Show Profile  Reply with Quote
so that exactly working as if you've written logic. what you could if you dont want to throw error in such cases is to first insert the memberdetails onto customer table, generate new member id and use it to insert to payment.
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  13:08:41  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

so that exactly working as if you've written logic. what you could if you dont want to throw error in such cases is to first insert the memberdetails onto customer table, generate new member id and use it to insert to payment.



Ok thank you!! Do you have time to help me look at another trigger?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 11/09/2008 :  13:14:29  Show Profile  Reply with Quote
welcome
post it. if not able to fix today will get back to it tommorow. (its 12 midnight here!)
Go to Top of Page

sas0riza
Yak Posting Veteran

56 Posts

Posted - 11/09/2008 :  13:20:24  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

welcome
post it. if not able to fix today will get back to it tommorow. (its 12 midnight here!)



Great, I really appreciate your help!

I think I got this trigger to work, but I have a different question.
Like with the money datatype, can you specify to 2 decimal places?

For example, if the Balance column for a record, is 11.5185. In actuality, it's 11.52. But when I insert my Payment record, I can't put 11.52 because then it'll say I have a balance of 0.15 and not 0.00. How to take care of that?
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000