1. Purpose and requirements

1. Learn how to write database stored procedures.
2. Master the method of establishing database triggers, observe the functions of triggers and setting trigger conditions and other related operations through experiments.
3. Complete the case for the teacher's class (optional)

2. Experiment preparation

1. Understand the T-SQL syntax for writing stored procedures and calls;
2. understand what triggers do;
3. Learn the T-SQL syntax for writing triggers.

3. Experimental content

(1) Stored procedure

1. Reserve knowledge

1.1 Create a stored procedure

If there is output, it is an output parameter, and if there is no output, it is an input parameter.

create procedure | proc stored procedure name
     @parameter name 1 {parameter data type}[=Defaults] [output], 
     @parameter name 2 {parameter data type}[=Defaults] [output],
     ....
as
    SQL_statements

1.2 Execute the stored procedure

exec stored procedure name parameter value 1 [output]，parameter value 2 [output]...

1.3 Modify the stored procedure

alter procedure stored procedure name
     parameter name {@parameter data type}[=Defaults] [output],
     parameter name {@parameter data type}[=Defaults] [output],
     ....
as
    SQL_statements

1.4 Delete the stored procedure

drop procedure stored procedure name

1.5 Modify the stored procedure name

[exec] sp_rename 'original stored procedure name','new stored procedure name'

1.6 Others

1. begin and end
2. Declare variables: declare @variable name data type (int,varchar,datetime,char)
3. The custom variable name should not be the same as the field name

📝 Example demo

-- Create stored procedure
create procedure cal
	@a int,
	@b int,
	@sub int output,
	@sum int output
as
	set @sub = @a - @b
	set @sum = @a + @b
go

-- call stored procedure
declare @sub int
declare @num int
exec cal 5,4,@sub output,@num output
select @sub,@sum

2. Create a stored procedure instance

Create a stored procedure getPractice in the studentdb database, and query the specified department (name) (as the input parameter of the stored procedure) for all students participating in the "practice" course study number, name, course number and course name, if the department does not exist, returns a prompt message.

create procedure getPractice
	-- Designated Faculty(name)(as an input parameter to a stored procedure)
	@name varchar(24) --mysql Medium means 24 means 24 characters sqlserver neither utf-8
as
	--1.Inquire about the students of the specified department --> D_ID
	--2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok
	--3.""Practice" course --> first look s_c_info Student-Course Correspondence
	--4.If the department does not exist, a prompt message is returned.
	if (select count(*) from D_Info where D_Name=@name) <> 0 --Indicate that there are departments
		begin
			select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='practice'
		end
	else
		print('Faculty does not exist')
go

3. Stored procedure processing

1️⃣ Execute the stored procedure getPractice respectively to query the student numbers, names, course numbers and course names of all students participating in the "Practice" course among the students of "School of Law" and "School of Materials Science and Engineering".

exec getPractice 'law school'

exec getPractice 'material science and Engineering School'

2️⃣ Use the system stored procedure sp_rename to rename getPractice to getPctStu

[exec] sp_rename 'getPractice','getPctStu'

3️⃣ Modify the stored procedure getPctStu to return the number of students participating in the practical course in the specified department, and use this stored procedure to verify the execution result with "law school" as the input parameter

alter procedure getPctStu
	-- Designated Faculty(name)(as an input parameter to a stored procedure)
	@name varchar(24) -- mysql 24 means 24 characters sqlserver neither utf-8
as
	-- 1.Inquire about the students of the specified department --> D_ID
	-- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok
	-- 3.""Practice" course --> first look s_c_info Student-Course Correspondence
	-- 4.If the department does not exist, a prompt message is returned.
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments
		begin
			select count(*) -- Number of query records(The same student number does not matter, it counts as multiple entries)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='practice'
		end
	else
		print('Faculty does not exist')
go

Validation: calling a stored procedure

exec getPctStu 'law school'

exec getPctStu 'law school'

4️⃣ Modify the stored procedure getPctStu to return the number of students participating in the practical course in the specified department.

alter procedure getPctStu
	-- Designated Faculty(name)(as an input parameter to a stored procedure)
	@name varchar(24) -- mysql Medium means 24 means 24 characters sqlserver neither utf-8
as
	-- 1.Inquire about the students of the specified department --> D_ID
	-- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok
	-- 3.""Practice" course --> first look s_c_info Student-Course Correspondence
	-- 4.If the department does not exist, a prompt message is returned.
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments
		begin
			-- distinct st_info.St_ID --> Filter out the same student number and merge them into one record
			select count(distinct st_info.St_ID)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='practice'
		end
	else
		print('Faculty does not exist')
go

Validation: Invoking a stored procedure call

exec getPctStu 'law school'

exec getPctStu 'law school'

⚠️Note: "Number of people" and "Number of people" are different. For a student, if he has participated in multiple practical courses, "Number of people" refers to the number of courses he has participated in, while "Number of people" is still 1.

(2) Trigger

1️⃣ Create a trigger with audit function in the studentdb database: the trigger name is tr_sc, and the function requirements: audit the update and insert operations on the score field in the s_c_info table, and record these operations in the sc_log table, which contains The following fields: operation type type, student number st_id, course number c_no, old score oldscore, new score newscore, operator uname, operation time udate, where the default value of operator setting is user, and the default value of operation time is system time.

Create the sc_log table:

create table sc_log( 
    type char(6),
  	st_id char(10),
  	c_no char(10),
  	oldscore int,
  	newscore int,
  	uname varchar(20) default 'user',
  	udate datetime default getdate()
)

Create a stored procedure:

create trigger tr_sc
on s_c_info
for update,insert
as
	if (select count(*) from deleted)<>0
	-- Description is an update operation
		insert into sc_log(type,st_id,c_no,oldscore,newscore)
		select 'update',s_c_info.st_id,s_c_info.c_no,deleted.score,inserted.score
		from s_c_info,deleted,inserted
		where s_c_info.st_id = deleted.st_id
		and s_c_info.c_no = deleted.c_no
	else
		insert into sc_log(type,st_id,c_no,newscore)
		select 'insert',inserted.st_id,inserted.c_no,inserted.score
		from inserted

Test: Calling a Stored Procedure

insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)

-- View Results
select * from sc_log

update s_c_info
set score = 99
where st_id = '0603060108'
and c_no = '9720013'

-- View Results
select * from sc_log

2️⃣ Create a trigger tr_updasc on the s_c_info table to monitor the update of grades. It is required that the updated grades cannot be lower than the ones before the update. If the new grades are lower, the operation will be cancelled and a prompt will be given, otherwise the update is allowed.

create trigger tr_updasc
on s_c_info
for update
as
	if update(score)
	begin
		declare @oldscore int
		declare @newscore int
		set @oldscore = (select score from deleted)
		set @newscore = (select score from inserted)
		if @oldscore > @newscore
		begin
			print('The new grade is not allowed to be lower than the old grade, and the update fails')
			rollback transaction
		end
	end

Test: Calling a Stored Procedure

update s_c_info
set score = 98
where st_id = '0603060108'
and c_no = '9720013'

(3) View storage

Use sp_helptext to view the code of stored procedures and triggers

sp_helptext 'stored procedure name'

🚩 Supplement: The demo SQL code I wrote when I was explaining at the Tencent conference

-- create procedure | proc stored procedure name
--      @parameter name 1 {parameter data type}[=Defaults] [output], 
--      @parameter name 2 {parameter data type}[=Defaults] [output],
--     ....
--as
--    SQL_statements

-- Calculate the sum of two numbers
create procedure cal
	@a int, -- input
	@b int, -- input
	@sum int output -- ginseng
as
	set @sum = @a + @b
go

-- define variable
declare @sum1 int
-- call stored procedure execute: implement
-- Execute the stored procedure syntax: exec stored procedure name parameter 1, parameter 2 [output]
exec cal 5,4,@sum1 output -- If it is a parameter, you must write output
select @sum1
go

-- delete stored procedure
-- drop table Table Name
drop procedure cal
go

alter procedure cal  
	@a int, -- input
	@b int -- input
as
	select @a+@b
go

-- Change the stored procedure name
-- grammar:[exec] sp_rename 'old stored procedure name','new stored procedure name'
sp_rename 'cal','newCal' -- there will be warnings

exec sp_rename 'newCal','cal' -- there will be warnings

-- begin end
alter procedure cal  
	@a int, -- input
	@b int -- input
as
begin
	select @a+@b
end

-- exist`studentdb database`create stored procedure`getPractice`，Inquire about the designated department(name)(as an input parameter to a stored procedure)middle
-- The student number, name, course number and course name of all students participating in the "practice" course. If the department does not exist, a prompt message will be returned.

drop procedure getPractice
-- varchar String type
create procedure getPractice
	-- Designated Faculty(name)(as an input parameter to a stored procedure)
	@name varchar(24) -- mysql Medium means 24 means 24 characters sqlserver neither utf-8
as
	-- 1.Inquire about the students of the specified department --> D_ID
	-- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok
	-- 3.""Practice" course --> first look s_c_info Student-Course Correspondence
	-- 4.If the department does not exist, a prompt message is returned.
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments
		begin
			select st_info.St_ID,st_info.St_Name,s_c_info.c_no,C_Info.C_Name
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='practice'
		end
	else
		print('Faculty does not exist')
go

-- call stored procedure
exec getPractice 'law school'

exec getPractice 'material science and Engineering School' 
	
select count(*) from D_Info where D_Name='material science and Engineering School'
select left('wkx cool',5)

-- Rename: remember to write single quotes
sp_rename 'getPractice','getPctStu',

-- Stored procedure name: getPctStu

-- Modify the stored procedure getPctStu，Returns the number of students participating in practical courses in a given department
go
alter procedure getPctStu
	-- Designated Faculty(name)(as an input parameter to a stored procedure)
	@name varchar(24) -- mysql 24 means 24 characters sqlserver neither utf-8
as
	-- 1.Inquire about the students of the specified department --> D_ID
	-- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok
	-- 3.""Practice" course --> first look s_c_info Student-Course Correspondence
	-- 4.If the department does not exist, a prompt message is returned.
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments
		begin
			select count(*) -- Number of query records(The same student number does not matter, it counts as multiple entries)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='practice'
		end
	else
		print('Faculty does not exist')
go

-- And use the stored procedure to verify the result of the execution with "law school" as the input parameter
go
exec getPctStu 'law school'
go
--         Modify the stored procedure again getPctStu，Returns the number of students participating in practical courses in the specified department.--> A student can only appear once
-- Original Title: Modifying Stored Procedures getPctStu，Returns the number of students participating in practical courses in a given department --> There can be multiple occurrences in the query table
alter procedure getPctStu
	-- Designated Faculty(name)(as an input parameter to a stored procedure)
	@name varchar(24) -- mysql Medium means 24 means 24 characters sqlserver neither utf-8
as
	-- 1.Inquire about the students of the specified department --> D_ID
	-- 2.Find out where the students in this department are(st_info) --> Check the first two characters of the student number D_ID it's ok
	-- 3.""Practice" course --> first look s_c_info Student-Course Correspondence
	-- 4.If the department does not exist, a prompt message is returned.
	if (select count(*) from D_Info where D_Name=@name) <> 0 -- Indicate that there are departments
		begin
			-- distinct st_info.St_ID --> Filter out the same student number and merge them into one record
			select count(distinct st_info.St_ID)
			from st_info,s_c_info,C_Info
			where left(st_info.St_ID,2) = (select D_ID
										   from D_Info
										   where D_Name=@name)
			and s_c_info.st_id=st_info.St_ID -- Find the course number of the course selected by each student
			and s_c_info.c_no=C_Info.C_No
			and C_Info.C_Type='practice'
		end
	else
		print('Faculty does not exist')
go

-- sc_log In the table, sc_log The table has the following fields:
-- Operation type type，student ID st_id，Course number c_no，old grades oldscore，new grade newscore，operator uname，Operation time udate，
-- where the operator sets the default value as user，The operation time defaults to the system time.

create table sc_log(
	type char(10),
	st_id char(10),
	c_no char(10),
	oldscore int,
	newscore int,
	-- uname varchar(24) default user, --Incorrect --> System keywords
	uname varchar(24) default 'user',
	udate datetime default getdate()    -- in difference sql huge difference in function names
)

-- exist studentdb Create a trigger with audit function in the database:
-- trigger named tr_sc，
-- Feature Request: Audit in s_c_info in the table score field update and insert operations, logging these operations to sc_log table

-- create trigger trigger name
-- on Table Name  -- works on that table
-- for [update,delete,insert]
-- as
-- SQL_statement

-- When the trigger takes effect, there will be two temporary tables deleted inserted
-- When deleting or updating operations: deleted  For deletes, old data For updates, old data  --> only one record
-- When performing an insert or update operation: inserted  new record -- only one record
  
go
create trigger tr_sc
on s_c_info
for update,insert
as
-- audit in s_c_info in the table score field update and insert operations, logging these operations to sc_log table --> log operations
	-- If it is an insert operation --> deleted table is empty
	-- If it is an update operation --> delete The table has an old piece of data
	if (select count(*) from deleted) != 0
		-- update operation
		begin
			if update(score) -- if modified score field
			insert into sc_log(type,st_id,c_no,oldscore,newscore) -- There are default values, we don't need to write
			-- deleted.score correspond oldscore
			-- inserted score correspond newscore
			select 'update',inserted.st_id,inserted.c_no,deleted.score,inserted.score
			from deleted,inserted
		end
	else  -- Indicates that this is an insert operation
		begin
			-- No oldscore ，Then ignore it and do not write, the default value in the generated record is null
			insert into sc_log(type,st_id,c_no,newscore) -- type is the key of the database, so it is blue
			select 'insert',inserted.st_id,inserted.c_no,inserted.score
			from inserted
		end

-- test:insert operation
insert into s_c_info(st_id,c_no,score)
values('0603060108','9720013',88)

select * from sc_log;

-- update record
update s_c_info
set score = 99
where st_id='0603060108'
and c_no='9720013'

select * from sc_log;

-- exist s_c_info Create a trigger on the table tr_updasc，Used to monitor updates to grades(update)
go -- end of previous statement
create trigger tr_updasc
on s_c_info
for update
as
	-- It is required that the score after the update cannot be lower than the score before the update. If the new score is lower, the operation will be cancelled and a prompt message will be given, otherwise the update is allowed.
	if update(score) -- It will only take effect if the grades are updated
		begin
			-- write separately
			declare @oldscore int
			declare @newscore int -- actual variable
			set @oldscore = (select score from deleted)
			set @newscore = (select score from inserted)
			if @newscore < @oldscore
				begin
					print('bad grades')
					rollback transaction -- Rollback, all operations will not take effect
				end
		end
go -- End this trigger execution

-- update record --sc_log
update s_c_info
set score = 100
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

-- Update record does not take effect --sc_log
update s_c_info
set score = 98 
where st_id='0603060108'
and c_no='9720013'

select *
from s_c_info
where st_id='0603060108'
and c_no='9720013'

go
sp_helptext tr_updasc -- show defined SQL statement
go