Stored Procedure and trigger application, stored procedure trigger

Stored Procedure and trigger application, stored procedure trigger

I. Tutorial Purpose

1. Master the methods and steps for creating a stored procedure;

2. Learn how to use stored procedures;

3. Master the methods and steps for creating a trigger;

4. Learn how to use the trigger.

Ii. experiment content

1. Create, execute, and delete stored procedures;

2. Create, execute, and delete a trigger.

Iii. Experiment steps

1. Create, execute, and delete stored procedures.

1) Use the S table, C table, and SC table in the S_C database to create a stored procedure with parameters-cjjicx. The stored procedure is used to return the student's student ID, elective course name, and course score from three tables when any student's name is entered.

Cjjicx creation statement:

Create procedure cjjicx

@ Name char (6)

AS

BEGIN

Select s. sno, C. cname, SC. grade from s, C, SC

Where s. sname = @ name and s. sno = SC. sno and SC. cno = C. cno

END

 

2) execute the cjjicx stored procedure and query the student ID, electives, and course scores of "Liu Chen.

Execute the statement:

EXEC cjjicx @ name = 'Liu chen'

Result description:

 

3) use the system stored procedure sp_helptext to view the text information of the stored procedure cjjicx.

View the result description:

Sp_helptext cjjicx

 

4) use the S table in the S-C database to create an encrypted Stored Procedure for it-jmxs. This stored procedure is used to return all information of students in the computer system when the stored procedure is executed.

Jmxs creation statement:

Create procedure mxs WITH ENCRYPTION

AS

BEGIN

SELECT * FROM S

END

5) execute the jmxs storage process to view the status of computer students.

Execute the statement:

EXEC mxs

Result description:

SP_HELPTEXT mxs

 

6) Delete the jmxs stored procedure.

Delete statement:

Drop procedure mxs

2. Create, execute, and delete a trigger.

1) Create an INSERT trigger named insert_xh in the S_C database and store it in the SC table. This trigger is used to insert a record to the SC table. If no student ID sno is inserted in the S table, a message is displayed, indicating that the record cannot be inserted. Otherwise, a message is displayed, indicating that the record is successfully inserted.

Insert_xh creation statement:

Create trigger insert_xh ON SC

AFTER INSERT

AS

BEGIN

IF (select count (*) FROM inserted join s on s. sno = inserted. sno) = 0

BEGIN

ROLLBACK TRAN

PRINT 'user cannot insert record'

END

ELSE

PRINT 'records inserted successfully'

END

2) create a DELETE trigger named dele_stu for the S table in the S_C database. The trigger is used to disable the deletion of records in the S table.

Dele_stu creation statement:

Create trigger dele_stu ON S

AFTER DELETE

AS

BEGIN

ROLLBACK TRAN

PRINT 'deleteprohibited'

END

3) create an UPDATE trigger named update_grade for the SC table in the S_C database. The trigger is used to disable the grade field in the SC table.

Update_grade Definition Statement:

Create trigger update_grade ON SC

For insert, UPDATE

AS

DECLARE @ grade1 INT;

DECLARE @ grade2 INT;

SELECT @ grade1 = inserted. grade FROM inserted

SELECT @ grade2 = deleted. grade FROM deleted

BEGIN

IF (@ grade1! = @ Grade2)

BEGIN

PRINT 'score cannot be modified'

ROLLBACK TRAN

END

END

4) Delete the update_grade trigger.

Run the following command to delete the update_grade trigger:

Drop trigger update_grade

Iv. Experiment Summary

The most basic