T-SQL statements create stored procedures and trigger exercises

SOURCE Page http://wenku.baidu.com/link?url= Ksdjjz1oftewg-wwfyzeshbmcll0e7csutscv3e6wv7py1xxhzk8-zz-yxlddef6tvkwcixivzldipzayahnk8n7c552drzxz5p_-fq_acq

Experimental process:
First, practice creating and invoking stored procedures and triggers that are taught in the classroom on the student database.
1. Create a instead of trigger, which requires the implementation of a function: Create a Delete type trigger on the T_student table Notallowdelete, when the record is removed, the display does not allow the deletion of the prompt information use students go
If exists (select name from sysobjects
where Name= ' Notallowdelete ' and type= ' tr ') drop trigger notallowdelete Go
CREATE Trigger Notallowdelete

if (@ @error <>0)----------@@ 代表 The system variable set @[email protected] @error return @errorvalue end

Execute the stored procedure:
declare @nl as int, @num as char (TEN), @returnvalue as int set @num = ' S99002 '
exec stu_age @num, @nl output
print ' School Number ' +rtrim (CAST (@num as char (10)) + ' student's age is ' +cast (@nl as
char (2)) + ' years ' execution result:

3. Create a stored procedure called Stu_info, which requires: Enter the student number, check the class,
Name, course name and selected course results.
CREATE proc Stu_info @xh as char (+) as begin
Select substring (s_number,4,1) as ' class ', s_name as ' name ', C_name as ' course name ', score as '
Results
From t_student INNER JOIN T_score
On S_number=s_num inner JOIN course on C_number=c_num where @xh =s_number end GO
Execute the stored procedure: Exec stu_info ' S99001 '

4. Find the factorial of a number (no return value) CREATE proc Jiecheng @i as int as
DECLARE @result as int declare @ii as int set @result =1 set @[email protected] while @i>1 begin
Set @[email protected]*@i

Set @[email protected] If @i>1 continue else begin print @ii
The factorial of print RTrim (cast (@ii as char (2)) + ' is: '------The output must use the conversion data type
function is cast, otherwise the following error will occur:
Print @result end End GO
Execute the stored procedure: declare @data as int set @data =5
EXEC Jiecheng @data Execution results: 5
The factorial of 5 is: 120
5. Ask for the factorial of a number, an input, an output. (with output parameters) CREATE proc Jiecheng
@i as int, @result as int output as
DECLARE @ii as int set @result =1 set @[email protected] while @i>0 begin
Set @[email protected]*@i set @[email protected] If @i>1 continue else break end GO
Execute the stored procedure:
declare @data as int, @sum as int set @data =5
exec Jiecheng @data, @sum output
The factorial of the print RTrim (cast (@data as char (2)) + ' is: ' Print @sum

The result of the execution is: 5, the factorial is: 120
(6) A stored procedure with a default value.
Enter the student number to inquire about the class, student's name, course name and the grade of the class selected. (stu_info1) CREATE proc Stu_info1 @num as char (ten) = ' S99001 ' as
Select substring (s_number,4,1) as ' class ', s_name as ' name ', C_name as ' course name ', score as ' score '
From t_student INNER JOIN T_score
On S_number=s_num inner JOIN course on C_number=c_num where @num =s_number GO
Execute stored procedure: EXEC stu_info1 Execution Result:

When you do not enter the specified number, the database automatically assigns a value of @num: S99001
Third, design the necessary trigger on BBS database.
? Publish the main post, the user points plus 10, the number of posts plus 1.
CREATE trigger Publish on Bbstopic for insert as
Begin
DECLARE @yhID as int
DECLARE @bkID as int
Select @yhID =tuid, @bkID =tsid from inserted update bbsuser set upoint=upoint+10 where @yhID =uid update bbssession
Set stopiccount=stopiccount+1

where @bkID =sid End
After the INSERT statement is executed:
Insert
Intobbstopic (Tid,tsid,tuid,ttopic,tcontents,tclickcount,tflag,tlastclickt,treplycount)
VALUES (' 6 ', ' 2 ', ' 1 ', ' Buy and sell mall ')
After performing this operation, the Bbsuser table in the uid=1 of the user ' Fire cloud evil Gods ' integral upoint plus 10;bbssseeion table in Sclickcount plus 1.
Thread, user points plus 1, the number of replies to the main post plus 1, update the last reply
Time, the number of posts in the section plus 1.
Create TRIGGER GT on Bbsreply
For Insert,update as begin
declare @yhID as int, @ztID as int, @bkID as int
Select @yhID =ruid, @ztID =rtid, @bkID =rsid from inserted update bbsuser set upoint=upoint+1 where @yhID =uid update bbstopic
Set treplycount=treplycount+1 where @ztID =tid
Update Bbssession
Set stopiccount=stopiccount+1 where @bkID =sid end
Execute INSERT statement: INSERT INTO
Bbsreply (rid,rtid,rsid,ruid,rtopic,rcontents) VALUES (' 6 ', ' 2 ', ' 1 ', ' 2 ', ' employment difficult ', ' unequal opportunity ')
In the Bbsuser table, the number of users in the Upoint will be added 1, in the Bbstopic table 2nd main posts of the reply volume (Treplycount) will be 7, the Bbssession table in the volume of posts (Stopiccount) will become 1601.

? Delete the thread, user points minus 20, the number of posts in the section minus 1.
Create trigger SCGT on bbsreply for delete,update as begin
declare @yhID as int, @bkID as int
Select @yhID =ruid, @bkID =rsid from deleted update Bbsuser set upoint=upoint-20 where @yhID =uid
Update Bbssession
Set stopiccount=stopiccount-1 where @bkID =sid end
Delete the main post, user points minus 50, section posting volume minus 1, delete all the thread.
Create trigger Sczt on Bbstopic for delete,update as begin
DECLARE @yhID as int declare @bkID as int
DECLARE @gtID as int
Select @yhID =tuid, @bkID =tsid from deleted update Bbsuser set upoint=upoint-50 where @yhID =uid
Update Bbssession
Set Stopiccount=stopiccount-1 Delete
From bbsreply
where rtid= (select TID from deleted)

T-SQL statements create stored procedures and trigger exercises