Triggers, indexes, transactions in the database

I. Flip-flop

A trigger is a special stored procedure that implements complex data integrity that automatically triggers execution when an update, insert, or DELETE statement is executed against a table or view to prevent incorrect, unauthorized, or inconsistent parameters on the data.

/* Create an UPDATE trigger */create trigger [dbo]. [Taocantype_update] on [dbo]. [table_taocantype]for update asupdate [dbo]. [Table_chosetaocantype] Set taocan=inserted. Taocanfrom [dbo]. [Table_chosetaocantype], inserted where [dbo]. [Table_chosetaocantype]. Taocanid=inserted. Taocanid

A trigger can maintain referential integrity between two tables, just like a foreign key. Foreign keys are more efficient at performing this task because they are tested before the data changes, unlike triggers that trigger after data changes.

Two. Index

(1) Definition:

1. An index is one of the objects of a database, and an index is a decentralized storage structure created to speed up the retrieval of rows of data in a table.

   An index is built on a table and is made up of index pages other than the data page

Create unique clustered index book_id_index--unique clustered index on book (book_id ASC) with FILLFACTOR=50/* Fill factor 50%*/

(2) Classification of indexes

1. Clustered index

   The physical order of the data table is the same as the order of the Index table, which arranges records based on a combination of one or more column values in the table

Create unique clustered index book_id_index--unique clustered index on book (book_id ASC) with FILLFACTOR=50/* Fill factor 50%*/

B. Non-clustered index

Create nonclustered index Student_course_indexon student_course (student_id asc,course_id ASC) with FILLFACTOR=50

Three. Business

(1) Definition:

A transaction is a sequence of operations that are either executed or not executed, which is an inseparable unit of work.

(2) Syntax steps:

1. Transaction start: Begin TRANSACTION

2. Transaction commit: Commit TRANSACTION

3. Transaction rollback: ROLLBACK TRANSACTION

4. Determines whether a statement executes an error: global variable @ @ERROR;

   @ @ERROR can only judge whether the execution of the current T-SQL statement is wrong, in order to determine whether all T-SQL statements in the transaction are wrong, we need to accumulate the error; Example: SET @[email protected][email protected] @error

Four. Stored Procedures

(1) Definition:

A stored procedure is a set of SQL code that is pre-edited and capable of implementing specific data manipulation functions. It is associated with a specific database and is stored on a SQL Server server.

(2) Benefits of stored procedures:

1. Re-use. Stored procedures can be reused to reduce the workload of database developers

2. Improve performance. The stored procedure is compiled when it is created, and it is not compiled when it is used in the future. General SQL statements are compiled once, so using stored procedures improves efficiency

3. Reduce network traffic. The stored procedure is located on the server, which only needs to pass the name of the stored procedure and parameters, thus reducing the traffic of the network transport

4. Security. Parameterized stored procedures can prevent SQL injection attacks, and Grant, Deny, and revoke permissions can be applied to stored procedures

Define a stored procedure:

create proc spaddstudents @name nvarchar () =null as BEGIN TRANSACTION-transaction INSERT INTO [Studentinfo]. [dbo].  [Students] (Name) VALUES (@name) if @ @ERROR <>0 begin rollback TRAN return end

Create a stored procedure that implements the addition calculation and takes the result of the operation as an output parameter

create proc spadd @value1 int, @value2 int, @result int output AS Select @[email Protected][email protected] Go

Executing Spadd stored Procedures

DECLARE @value1 int DECLARE @value2 int DECLARE @result int set @value1 =1 set @value2 =1 exec spadd @value1, @value2, @r Esult Output Print convert (char (5), @value1) + ' + ' +convert (char (5), @value2) + ' = ' + convert (char (5), @result)

Reference: http://www.xuebuyuan.com/2041347.html

This article is from the "sunshine225" blog, make sure to keep this source http://10707460.blog.51cto.com/10697460/1790465

Triggers, indexes, transactions in the database