Dird (4/20/2013)

---

It's hard to say without knowing more about the customer_rentals DDL. Would the rentals table even be updated or only insert/delete? Maybe something like this?

CREATE TRIGGER dbo.tr_num_rented_update

ON dbo.customer_rentals FOR UPDATE AS

DECLARE

declare @prev INT;

declare @curr INT;

BEGIN

select top 1 @prev=d.movie_id, @curr=i.movie_id from deleted d,inserted i;

if(@prev!=@curr)

BEGIN

UPDATE m SET num_rentals = num_rentals -1

FROM dbo.movies AS m where m.movie_id = @prev;

UPDATE m SET num_rentals = num_rentals +1

FROM dbo.movies AS m m.movie_id = @curr;

commit;

END

END

GO

This is my first attempt to write TSQL (googled some syntax) so I'm not sure if it will compile but I guess you can understand my idea & fix the syntax. You should know that this will only work if updating 1 row (I guess? unless the trigger hits for every row separately even if you did an update with no where clause?)...but I guess updates here are unlikely to be on more than 1 row at a time? If you need to cater to muliple simultaenous row updates then you'll probably need to use 1/2 cursors.

Dird