Turn!! Stored procedures and triggers

1. What are stored procedures and triggers?

Stored procedures cannot significantly improve the execution speed of the system, but also improve efficiency to ensure consistency.

1.1 Stored procedures: A database object that places the statements that are responsible for different functions in order to be reused.

1.2 Features:

1.3 Classification of stored procedures

Divided into five categories, system stored procedures, user-defined stored procedures, temporary stored procedures, remote stored procedures, extended stored procedures.

Temporary stored procedures are also divided into local temporary stored procedures, global temporary stored procedures.

category	description
System stored procedure	stored in master, starts with the SP, does not need to add the library name when called, if the parameter is a reserved word or database object, enclose in single quotation marks.
User-defined stored procedure	A user defines a stored procedure in a database for the completion of certain functionality.
Temporary stored procedure	Local temp: #开头, In tempdb, the connection is dropped and automatically deleted, and the library is used.
	Global Temp: # #开头, placed in tempdb and automatically deleted after the connection is disconnected, all libraries of this connection can be used. Note naming.
Remote stored procedure	Stored procedures located on a remote server. Execute through distributed queries and execute.
Extended stored procedure	External program write stored procedure, XP start, dynamic link library form exists, also put in master

1.4 Stored Procedure Benefits

1.5 Comparison of stored procedures and views

1.6 Creating a stored procedure

Format:

Example:

CREATE PROCEDURE Showallstudent

As

Begin

SELECT * FROM Students

End

1.7 Executing stored procedures

Format: EXEC procedure_name

Example: EXEC showallstudent

1.8 stored procedure with input parameters

Format:

Example:

CREATE PROCEDURE Selectstudentbystu_no

@sno char (12) = ' 200501020319 '

As

Begin

SELECT * from students where [email protected]

End

1.9 Executing a stored procedure with input parameters

Format:

Example:

EXEC selectstudentbystu_no

@sno = ' 200501030218 '

When the parameters are relatively small, parameters can be passed by location.

Example:

exec selectstudentbystu_no ' 200501030218 '

1.10 stored procedure with output parameters

Format:

Example:

CREATE PROCEDURE Getstudentcountbystu_sex

@sex char (2) = ' Male ',

@count int=0 Output

As

Begin

Set

@count = (select count (*) from students where [email protected])

Print @count

End

Executing a stored procedure with output parameters

Example:

Declare @sex char (2), @count int

Set @sex = ' Male '

exec getstudentcountbystu_sex @sex, @count

1.11 Deleting a stored procedure

Drop procedure Getpostsbyblogid

1.12 Updating stored procedures

Alter PROCEDURE Getpostsbyblogid

(@blogid varchar (50))

As

Select top 5* from Posts where [email protected]

1.13 Renaming stored Procedures

Modified in the Object Browser.

1.14 recompiling Stored Procedures

There are three ways to do this:

1) Use the WITH Recompile statement when creating.

2) Set recompile during execution

3) Call system stored procedure recompile

2. triggers

A stored procedure that is executed by an event trigger when the base table is modified.

The function is to guarantee referential integrity and data integrity that cannot be guaranteed by the primary key and foreign keys.

Trigger principle:
Triggers are very similar to stored procedures, and triggers are also sets of SQL statements, the only difference being that triggers cannot be invoked with an EXECUTE statement, but are automatically triggered (activated) when a user executes a Transact-SQL statement. A trigger is a stored procedure that executes when a data in a specified table is modified. It is common to enforce referential integrity and consistency for logically related data in different tables by creating triggers. Because the user cannot bypass the trigger, it can be used to enforce complex business rules to ensure the integrity of the data. Triggers are different from stored procedures, and triggers are executed primarily through event execution, and stored procedures can be called directly by the name of the stored procedure name. When you perform operations such as update, INSERT, and delete on a table, SQL Server automatically executes the statements defined by the trigger, ensuring that the processing of the data must conform to the rules defined by those SQL statements.
The role of triggers:
The main function of a trigger is that it can achieve complex referential integrity and data consistency that cannot be guaranteed by the primary key and foreign keys. It enables cascade modification of related tables in a database, enforces more complex data integrity than check constraints, and customizes operational messages, maintains denormalized data, and compares the state of data before and after modification. Unlike check constraints, triggers can reference columns in other tables. Use triggers to implement complex referential integrity in the following situations, forcing integrity between data. Create a multiline trigger, and when you insert, UPDATE, and delete multiple rows of data, you must write a trigger that handles multiple rows of data. Perform a cascade update or cascade delete such an action. Cascading modifies all related tables in the database. Undo or rollback an operation that violates referential integrity to prevent unauthorized modification of data.
The difference between a trigger and a stored procedure:
The main difference between a trigger and a stored procedure is how the trigger runs. The stored procedure must have a user, application, or trigger to display the call and execute, and the trigger is automatically executed or activated when a specific time occurs, regardless of the user or application in the database in which the connection is made. The trigger executes when a row is inserted, updated, or deleted, and also depends on how the trigger was created, using an UPDATE trigger when the update occurs, using an INSERT trigger when insert occurs, and using a delete trigger when the delete occurs.

2.1 Advantages OF Triggers

Triggers can contain complex processing logic that is primarily used to maintain low-level data integrity. The advantages are as follows.

2.2 Create a trigger

To delete a trigger:

Example:

Create Trigger Dropstudent

On students

For delete

As

print ' successfully deleted a piece of data. '

UPDATE Trigger:

Create Trigger Updatestudentname

On students

For update

As

If Update (stu_name)

Begin

print ' Cannot update primary key, student number. '

ROLLBACK TRANSACTION

End

Update students set

Stu_name= ' five elder brother '

where stu_no= ' 200501020319 '

Delete trigger: Slightly

To view a trigger:

1) using the system's stored procedures view: exec sp_helptrigger Students

using system tables: select name from sysobjects where type= ' TR '

2) Manager View slightly

Delete trigger: drop Trigger Updatestudent

To modify a trigger:

Rename : sp_rename Dropstudent,deletestudentitem

To start and stop a trigger:

ALTER TABLE students enable trigger all

Turn!! Stored procedures and triggers