Database-based "stored procedures and triggers"

Database-based "stored procedures and triggers"

The thought of summing up the stored procedure and trigger is a bit confusing ,. This is because stored procedures and triggers are relatively advanced and cannot be grasped. As a result, I have been dragging on to the conclusion. But sooner or later, you will have to pay back the problem, and you can only sum up your head. This is also your own accumulation. What we need to do is to accumulate resources and strive for great progress.

What is a stored procedure?

Stored Procedure
Procedure) is a set of SQL statements in a large database system that are compiled and stored in the database to complete specific functions, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters.

Storage Process Classification

1. system stored procedures. It is stored in the master and starts with sp. You do not need to add the database name when calling it. If the parameter is a reserved word or database object, it is enclosed by single quotation marks.

2. You can define a stored procedure. The user defines the stored procedure in the database to complete certain functions.

3. Temporary stored procedures. Local temporary: # starts with "#", which is placed in tempdb. It is automatically deleted after the connection is disconnected. This database is used. global temporary: starts with # and is placed in tempdb. After the connection is closed, it is automatically deleted. All databases connected can use this function. note.

4. The stored procedure is stored on the remote server. It is executed through Distributed Query and execute.

5. Extended Stored Procedures stored procedures written by external programs. The stored procedures start with XP and exist in the form of dynamic linked libraries, which are also stored in the master

What are the advantages of stored procedures?

1. Fast. Before running the stored procedure, the database has analyzed its syntax and provided an optimization execution plan. That is to say, the stored procedure has been compiled before it is called, so the stored procedure can be executed very quickly.

2. stored procedures can be reused to reduce the workload of database developers.

3. ensure data security. Through the stored procedure, users without permissions can indirectly access the database under control, thus ensuring data security.

4. ensure data integrity. Through the stored procedure, related actions can be taken together to maintain the integrity of the database.

Although the trigger has so many advantages, the more it is not used, the better it is. If you use a large number of stored procedures in a program system, it will inevitably lead to a complicated data structure, which will be quite difficult to maintain the system.

 

1. The statement for creating a stored procedure is as follows:

Createprocedure stored procedure name

@ [Parameter name] [type], @ [parameter name] [type]

As

Begin

Custom Functions

End

2. Call the Stored Procedure

Exec sp_name [parameter name]

3. delete a stored procedure

Drop proceduresp_name

I think that a stored procedure is a special type of function. We can directly call it as long as we give it proper parameters. Of course, there are some minor differences between functions and stored procedures, but it is not necessary to study them further. Note that you cannot delete another stored procedure in one stored procedure, but can only call another stored procedure.

What is a trigger?

In terms of definition, a trigger is actually a stored procedure, but its execution is not called by a program, nor is it manually started, but triggered by an event. What is triggered by an event? In fact, this is the same as the Click Event of the botton button, except that the trigger is triggered by insert, update, and delete actions, while the click event of botton is triggered by the click action.

Advantages of triggers

1. Automatic execution. The trigger is activated immediately after any changes (such as manual input or application operations) are made to the table data.

2. Cascade updates. The trigger can be changed through the relevant tables in the database, which is safer and more reasonable than writing the Code directly at the front-end.

3. Strengthen constraints. Triggers can reference columns in other tables to implement more complex constraints than check constraints.

4. Track changes. Triggers prevent unauthorized specified updates and changes in the database.

5. Force the business logic. Triggers can be used to execute management tasks and forcibly affect complex business rules of the database.

 

The creation statement is as follows:

2.2 create 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 'Primary key cannot be updated, student number .'

Rollback transaction

End

 

Update students set

Stu_name = 'tianqi'

Where stu_no = '123'

 

View the trigger:

1) use the system's stored procedure to view: exec sp_helptrigger students

 

Use the system table: Select name from sysobjectswhere type = 'tr'

 

Delete trigger: Drop trigger updatestudent

 

Modify the trigger:

 

Rename: sp_renamedropstudent, deletestudentitem

Start and Stop triggers:

Alter table students enable triggerall