数据库存储过程和触发器

--创建存储过程
CREATE PROCEDURE titles_sum @TITLE varchar(40),@SUM money OUTPUT
AS
SELECT @SUM = SUM(price)
FROM titles
WHERE title LIKE @TITLE
GO
DECLARE @TOTALCOST money
EXECUTE titles_sum 'The%', @TOTALCOST OUTPUT
select @TOTALCOST
go
CREATE PROCEDURE Oakland_authors
AS 
SELECT au_fname, au_lname, address, city, zip
FROM authors
WHERE city = 'Oakland'
and state = 'CA'
ORDER BY au_lname, au_fname
GO
--sp_helptext Oakland_authors
ALTER PROCEDURE Oakland_authors
AS 
SELECT au_fname, au_lname, address, city, zip
FROM authors
WHERE state = 'CA'
ORDER BY au_lname, au_fname
GO
--sp_helptext Oakland_authors
--提交事务后，所有书籍支付的版税增加 10%。
begin transaction MyTransaction
update roysched
set royalty = royalty * 1.10
commit transaction MyTransaction
--rollback transaction MyTransaction
select 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)go
insert 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_modify
on temptrigger
for insert,update
as
begin
  if (select temp_age from inserted) > 15
    begin
      rollback transaction
      print '年龄不能超过15岁！'
    end
end
--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_delete
create trigger temptrigger_delete
on temptrigger
for delete
as
begin
  print @@rowcount
  if @@rowcount > 1
  begin
    rollback transaction
    print '一次删除记录不能多于1条'
  end
end
--delete from temptrigger
--delete from temptrigger where id_temp='01'