SQL Classroom notes-stored procedures and triggers

---restore content starts---

2017.11.21
Stored procedures and triggers

1. The role of the stored procedure

When SQL Server creates an application, you can store and execute the program in two ways:

1). Store the program locally, and then create an application that SQL Server sends commands and processes the results

2). Store the program as a stored procedure in a SQL Server server, and then create an application that executes the stored procedure and processes the results. SQL Server recommends using this

2. Stored procedure (store Procedure) is a set of T-SQL statements that are compiled to perform a specific function and are stored in a SQL Server server-side database. Use stored procedures to accelerate SQL statement execution

3. Advantages:
* * The stored procedure has security and ownership links that can perform all rights management
The stored procedure is already registered on the server, which can improve the efficiency of T-SQL statement execution.
The stored procedure can improve the security of your application. Can prevent SQL embedded attacks
Stored procedures can be sent to reduce network traffic


4. Three basic types of stored procedures:
1. System Stored Procedures
2. User-defined stored procedures
3. Extended Stored Procedures

5. Classification of stored procedures:
1. System Stored Procedures
A system stored procedure is a special kind of stored procedure that performs the basic management of the database, stored in the source database of SQL Server 2008, usually prefixed with "sp_"

2. user-defined stored procedures: user-defined stored procedures refer to user-defined stored procedures
A T-SQL stored procedure refers to a set of T-SQL statements that can accept and return user-supplied parameters

3. Extended stored Procedures :

An extended stored procedure is an external routine that dynamically loads and runs in the address space of a SQL server2008 instance

What stored procedures have been learned:
1.SP_HELPDB: View all database names and sizes
2.sp_help: Displaying information for a table
3.sp_renamedb ' Old_dbname ', ' new_dbname ': Renaming a database with SQL
4.select @ @servername: Querying the name of the local server
5.sp_helpindex table Name: View index Information
6.sp_rename ' Old_viewname ', ' new_viewname ': renaming views

6.2.2 Creating stored procedures in T-SQL language

SQL server2008 can use the CREATE PROCEDURE statement to define stored procedures with the following syntax:

Create {proc|procedure} stored procedure name: [; value]

[{@ parameter data type}] [Output]

as {<sql statement >[;] [... n]}

1. Create a simple stored procedure:
Example 1. Create a simple stored procedure PROC_KC, which does not include any parameters, contains only a simple SELECT statement that is used to query all course information

Use students
Go
CREATE PROCEDURE PROC_KC
As
SELECT * FROM Course

Example 2. Create a stored procedure with input parameters Proc_s_g_c. The query refers to the student performance information of the course, which, by default, returns the results of the ' 1 ' Course:
Use students
Go
CREATE PROCEDURE Proc_s_g_c
@cno char (8) = ' 1 '
As
Select Sno,cname,grade from COURSE,SC
where course.cno=sc.cno and [email protected]

Example 3. Create a stored procedure proc_avg with parameters and output parameters to return the average of the students ' courses

Use students
Go
Create proc Proc_avg
@sno char (10) = ' 95001 ',
@aver int Output
As
Select @aver =avg (grade) from SC
where [email protected] GROUP by Sno

2. Execute the stored procedure:
After the stored procedure is created, you can use the EXEC statement to execute the stored procedure, syntax:
[(Exec | execute)]
{[@ return status =]
{@ stored procedure variable}
     }
1. Execute a simple stored procedure
Perform the simple stored procedure PROC_KC created above to display information for all courses
Use student
Go
EXEC PROC_KC
2. Execute a stored procedure with parameters: Execute stored procedure proc_s_g_c to display a specific course information
Use students
Go
EXEC proc Proc_s_g_c @sno =1
3. Execute a stored procedure proc_avg with input and output parameters to return the average score for the student's course
declare @sno int, @aver int
Set @sno =95006
exec proc_avg @sno, @aver output
print ' average score: ' +cast (@aver as char (3))

3. Modifying a stored procedure with a T-SQL statement
1. Modifying stored procedures
SQL server2008 can be implemented with the ALTER PROCEDURE statement in the form of a command, with the following basic syntax:
alter{proc|procedure} stored procedure name [; value]
[{@ parameter data type}][output]
as{<sql statement >}[;] [... n]

Example: Modifying a stored procedure proc_avg to output an average score for a course:

Use students
Go
ALTER PROC PROC_AVG
@cno Int=1,
@aver int Output
As
Select @aver =avg (grade) from SC
where [email protected] GROUP by Sno

4. Delete a stored procedure with a T-SQL statement
Grammar:
drop procedure {PROCEDURE}[1...N]

Delete the stored procedure created above PROC_KC:

drop procedure PROC_KC

Trigger:

A trigger is actually a special type of stored procedure that is executed automatically when some T-SQL statements are executed:

Type of Trigger:
(1) DML triggers (performed when data manipulation language Insert,update,dalete events occur in the database server)
In SQL 2008, there are two tables defined for each DML trigger, one for inserting the Insert table and one for deleting the delete table.
(2) DDL triggers (triggered by the response data Definition language Create,alter,drop statement)

To divide a DML trigger into an INSERT Delete Update3 class

In SQL server2008, two special tables are defined for each DML trigger, one for inserting insteadand one for deleting the delete table . These two tables are built into the memory of the database server and are managed by the system logic

Tables (virtual tables), rather than physical tables that are actually stored in the database. For both tables, the user has only modified permissions, no Read permissions

To create a trigger:
1. Create a DML trigger, syntax:
CREATE TRIGGER Trigger name
on{Table name | View name}
{{For|after|instead of}{[dlete][,][insert][,][uptate]}}
as
SQL statement [.. N.]}}

Example 1: An UPDATE trigger is created for the student table, and when a student's number is updated, the trigger cascade update is activated in the SC table
Academic number in the relevant record:
CREATE TRIGGER Tri_upd_student
On student for update
as
declare @oldsid char (TEN), @newsid char (TEN)
Select @oldsid =deleted.sno, @newsid =inserted.sno
From inserted,deleted where
Inserted.sname=deleted.sname
Update SC Set [email protected] where [email protected]

View results:
Update student set sno=95001 where sno=85001

2.

Example 2:course table and SC table is the relationship between master and slave table, when the course table is deleted when the operation, the first should determine whether the SC table has related records,
If so, the deletion is forbidden.
CREATE TRIGGER Tri_del_course
On course
instead of delete
as
begin
if exists (select CNO from SC where cno in (select CNO from deleted))
The print ' SC table has related records and cannot be deleted '
Else
Delete from course where CNO in (select CNO from deleted)
End

3. Manage triggers:
1. View the trigger:

You can use the system stored procedure sp_help,sp_helptext to view information about the trigger, and the syntax format uses the following:

SP_HELP: General information for query triggers, such as trigger name, property, type, creation time

　　　　　Sp_help ' Trigger name '

Sp_helptext: Used to view the body information for a trigger "

sp_helptext ' trigger name '
2. Delete the trigger:

You can delete a trigger by using the DROP TRIGGER statement. You can delete one or more triggers at the same time, syntax:

Drop trigger trigger name [... n]

On {database|all server}

[;]

　　　　　　Delete the created trigger Tri_del_student

Drop Trigger tri_del_student

3. Disable and enable triggers

Grammar:

ALTER TABLE TABLE_NAME

{disable|enable} Trigger

{all|trigger_name[,... n]}

　　　　prohibit triggers built on the student table tri_up_student 　　　

ALTER TABLE student

Disable Trigger tri_up_student

Practice

--Task 1
--Create a simple stored procedure PROC_KC, which contains no parameters and contains only a simple select query statement. This stored procedure is used to query information for all courses.
Use students
Go
CREATE PROCEDURE PROC_KC
As
SELECT * FROM Course

--Task 2
--Create a stored procedure with input parameters Proc_s_g_c, query the student score information for the specified course, and return the results of the "2" course by default.
Use students
Go
CREATE PROCEDURE Proc_s_g_c
@cno char (8) = ' 2 '
As
Select Sno,cname,grade from COURSE,SC
where course.cno=sc.cno and [email protected]

--Task 3
--Create a stored procedure proc_avg with input parameters and output parameters to return the average score of the course No. 95001 students have learned.
Use students
Go
Create proc Proc_avg
@sno char (10) = ' 95001 ',
@aver int Output
As
Select @aver =avg (grade) from SC
where [email protected] GROUP by Sno

--Task 4
--Perform task 1 to create a simple stored procedure proc_kc that displays information for all courses.
Use students
Go
EXEC PROC_KC

--Task 5
--A stored procedure proc_avg with input parameters and output parameters created by Task 3, used to return the average score of the course taken by student number No. 95006.
declare @sno int, @aver int
Set @sno =95006
exec proc_avg @sno, @aver output
print ' Average score: ' +cast (@aver as char (3))

--Task 6
--Modify the simple stored procedure Proc_avg created by task 3 to output the average score for a course.
Use students
Go
ALTER PROC PROC_AVG
@cno Int=1,
@aver int Output
As
Select @aver =avg (grade) from SC
where [email protected] GROUP by Sno

--Task 7
--delete The simple stored procedure PROC_KC created by task 1.
drop procedure PROC_KC

--Task 8 creates an update trigger for the student table, and when a student's number is updated, activates the trigger cascade to update the number information in the relevant score record in the SC table.
Create Trigger Tri_upd_student
On student for update
As
Declare @oldsid char (TEN), @newsid char (10)
Select @oldsid =deleted.sno, @newsid =inserted.sno
From inserted,deleted where
Inserted.sname=deleted.sname
Update SC Set [email protected] where [email protected]

--View results:
Update student set sno=85001 where sno=95001

--Task 9 create instead of triggers student tables and SC tables are the relationships between master and slave tables, and when deleting operations on student tables, first determine if there are related records in the SC table and, if so, prohibit deletion.
Create Trigger Tri_del_course
On course
Instead of delete
As
Begin
If exists (select CNO from SC where cno in (select CNO from deleted))
The print ' SC table has related records and cannot be deleted '
Else
Delete from course where CNO in (select CNO from deleted)
End

--Task 10 View general information for triggers
Sp_help Tri_del_course

--Task 11 View the body information of the trigger.
Sp_helptext Tri_del_course

--Task 12 prohibits the triggers that are built on the student table.
ALTER TABLE student
Disable Trigger Tri_upd_student

--Task 13 deletes the created task 8 trigger.
Drop Trigger Tri_upd_student

SQL Classroom notes-stored procedures and triggers