select Price*1.05 from Videos where VideoID = @VideoID

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

---

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.

---

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

---

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

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

---

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

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

---

quote:

---

Originally posted by visakh16

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

---

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

---

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)

---

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.

---

quote:

---

Originally posted by visakh16

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

---