sql - Why won't my master table column increment in my trigger? -

i need create trigger keep track of number of times movie rented business blockbuster. need separate trigger insert, delete , update.

the column tracks number of times rented called num_rentals , has datatype of int. column part of movies table has *movie_id (pk*), movie_title, release_year, movie_description, , num_rentals. customer_rentals table has item_rental_id(pk), *movie_id(fk*), customer_id.

i searched , found similar thread here , tried using supplied answer, no avail. executed following string without errors saw no change in num_rentals column when inserted data either movie or customer_rentals table. doing wrong?

create trigger dbo.tr_num_rented_insert on dbo.customer_rentals     insert     begin         update  m         set     num_rentals = num_rentals + 1            dbo.movies m                 inner join inserted on m.movie_id = i.movie_id ;         end 

i added num_rentals field table later , need know how initialize field value 0 records in movies table.

i want understand as want answer assistance appreciated. read there may more efficient ways manage type of data, way instructor wants it. thank in advance!

i suspect problem null num_rental column. you're adding null 1, resulting in null.

personally, i'd set num_rentals column non-nullable default of zero, assuming can't that, use isnull().

like so

alter trigger dbo.tr_num_rented_insert on dbo.customer_rentals     insert     begin         update  m         set     num_rentals = isnull(num_rentals,0) + 1            dbo.movies m                 inner join inserted on m.movie_id = i.movie_id ;     end 

however, though works, there problem it; if add multiple rows customer_rentals, it's going increment one. personally, i'd change trigger account that.

like this

alter trigger dbo.tr_num_rented_insert on dbo.customer_rentals     insert     begin         update  m         set     num_rentals = isnull(num_rentals,0) + (select count(*) inserted movie_id = m.movie_id)            dbo.movies m           movie_id in (select movie_id inserted)     end 

as far duplicate information goes, aaron right needlessly redundant , in experience, type of thing gets out of sync. such simple database, num_rentals column overkill (to generous), movie database contrived example teach concept. basically, want calculated values accessible or filtered on. take rep example, assume don't recalc every time display it.