Triggers:  Examples from pubs2 database

1> use pubs2
1> /*	Trigger to prevent deleting title if selling 		*/
/*								*/
2> sp_helptext deltitle
 # Lines of Text 
 --------------- 
               1 

(1 row affected)

	 text                                                                                                                                                                                                                                                            
 
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

	 create trigger deltitle
on titles
for delete
as
if (select count(*) from deleted, salesdetail
where salesdetail.title_id = deleted.title_id) >0
begin
	rollback transaction
	print "You can't delete a title with sales."
end
 

(1 row affected)
(return status = 0) 
1> /*  Trigger to recalculate total sales when a title is	*/
/*  added, deleted, or something about it changed in salesdetail.*/
/*								 */
2> sp_helptext totalsales_trig
 # Lines of Text 
 --------------- 
               3 

(1 row affected)

	 text                                                                                                                                                                                                                                                            
 
	--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 


	 create trigger totalsales_trig
on salesdetail
for insert, update, delete
as
/* Save processing:  return if there are no rows affected */
if @@rowcount = 0
	begin
		return
	end

/* add all the new values */
/* use isnull:  a null value in the titles table 

	 means 
**              "no sales yet" not "sales unknown"
*/
update titles
        set total_sales = isnull(total_sales, 0) + (select sum(qty)
		from inserted
		where titles.title_id = inserted.title_id)
	where title_id in (select title_id from inserted)
 

	 
/* remove all values being deleted or updated */
update titles
        set total_sales = isnull(total_sales, 0) - (select sum(qty)
		from deleted
		where titles.title_id = deleted.title_id)
	where title_id in (select title_id from deleted)
 

(3 rows affected)
(return status = 0) 
1> /*	*/