SQLServer存储过程与触发器课堂练习及答案.docxVIP

存储过程与触发器课堂练习及答案 创建一个存储过程，显示所有价格在 15 美元以下的书的书名，类型，价格。CREATE PROCEDURE show_title AS SELECT title,type,price FROM titles WHERE price 15 GO EXEC show_title 把价格作为参数，创建一个能显示在某两个指定价格之间的书的书名，类型，价格。CREATE PROCEDURE show_title2 @price1 money,@price2 money AS SELECT title,type,price FROM titles WHERE price between @price1 and @price2 GO show_title2 12,20 使用 OUTPU 参数，创建一个计算圆柱体体积的存储过程。并执行它。CREATE PROCEDURE comp_area @r smallint, @h smallint, @result decimal(10,2) OUTPUT AS SET @result = PI()*SQUARE(@r)* @h GO DECLARE @answer decimal(10,2) EXECUTE comp_area 2,3, @answer OUTPUT SELECT The result is: , @answer A) 建立 price_change 表，准备用来存放书的价格变化信息，有以下几列： title_id, type, old_price, new_price, change_date, operat。or B) 建立一个更新触发器，一旦 title表s create table price_change 发生更新，立即把相关信息存放到 price_change 表中。 ( title_id varchar(20), type varchar(20), old_price money, new_price money, change_date datetime, operator varchar(20))go create trigger tri_price on titles for update as insert into price_change select ,,,,getdate(),user_name() from deleted o JOIN inserted n ON = go update titles set price = price* 修改练习 4，使得只有当 price列被更新时，才会触发触发器。create trigger tri_price on titles for update as if update(price) begin insert into price_change select ,,,,getdate(),user_name() from deleted o JOIN inserted n ON = end go 创建一个存放书的编号、书名、类型、价格、对应作者的编号、姓名、电话、住址的视 图。use pubs go create view v_titledetail as select , title, type, price, , au_lname, au_fname, phone, address from titles t,titleauthor ta,authors a where = and = 为这个视图创建一个 Instead of更新触发器， 把对视图的更新放到触发器里面来做。 ( 假 设， 我们只允许更新这个视图的某几个列： price, phone, addres)s on v_titledetail instead of update as create trigger tri_titledetail declare @price money, @phone varchar(20), @address varchar(40), @title_id varchar(20), @au_id varchar(20) select @title_id = title_id, @price = price, @au_id = au_id, @phone= phone ,@address = address from inserted update titles set price = @price where title_id = @title_id update authors set phone = @phone, address = @address where au_