数据库存储过程和触发器

--创建存储过程CREATE PROCEDURE titles_sum @TITLE varchar(40),@SUM money OUTPUTASSELECT @SUM = SUM(price)FROM titlesWHERE title LIKE @TITLEGODECLARE @TOTALCOST moneyEXECUTE titles_sum 'The%', @TOTALCOST OUTPUTselect @TOTALCOSTgoCREATE PROCEDURE Oakland_authorsAS SELECT au_fname, au_lname, address, city, zipFROM authorsWHERE city = 'Oakland'and state = 'CA'ORDER BY au_lname, au_fnameGO--sp_helptext Oakland_authorsALTER PROCEDURE Oakland_authorsAS SELECT au_fname, au_lname, address, city, zipFROM authorsWHERE state = 'CA'ORDER BY au_lname, au_fnameGO--sp_helptext Oakland_authors--提交事务后，所有书籍支付的版税增加 10%。begin transaction MyTransactionupdate royschedset royalty = royalty * 1.10commit transaction MyTransaction--rollback transaction MyTransactionselect royalty from roysched--select @@trancount--1.创建试验实验表create table temptrigger( id_temp varchar(2) not null primary key,  temp_name varchar(10) null,  temp_age int null)goinsert temptrigger values('01','张三','10') insert temptrigger values('02','李四','11') insert temptrigger values('03','王五','12') insert temptrigger values('04','赵六','11') select * from temptrigger  go--2.创建insert , update触发器create trigger temptrigger_modifyon temptriggerfor insert,updateasbegin  if (select temp_age from inserted) > 15    begin      rollback transaction      print '年龄不能超过15岁！'    endend--insert temptrigger values('04','大朋','17') --insert temptrigger values('05','大朋','17') --insert temptrigger values('05','大朋','14') --update temptrigger set temp_age='18' where id_temp = '01'--update temptrigger set temp_age='9' where id_temp = '01'-3.创建delete 触发器：drop trigger temptrigger_deletecreate trigger temptrigger_deleteon temptriggerfor deleteasbegin  print @@rowcount  if @@rowcount > 1  begin    rollback transaction    print '一次删除记录不能多于1条'  endend--delete from temptrigger--delete from temptrigger where id_temp='01'