SQL Server stored procedures and triggers

Stored Procedures

I. Definition
A stored procedure is a set of SQL statements that completes a specific function, is compiled and stored in a database, and the user develops a stored procedure
Name and give the parameter to execute it, typically writing a complex statement as a stored procedure
Two. Statements
1. Format
Create proc Stored procedure name as related operations go
exec stored Procedure name
2. Example

(1) Create a stored procedure test

Update points when adding information to GroupInfo

(2) Change of process function name

(3) Delete a stored procedure

1 CREATE proc Test2  as 3DECLARE @ageint   //declaring Variables4 Select@age =age fromUserInfowhereUerid=2  //assigning values to variables5INSERT into groupinfo values ("newly published articles")6Update UserInfoSet[Email protected]+1 whereUserid=27 Go8 9Sp_rename Test,newproc//Renaming a stored procedureTen exec Newproc One  Adrop proc Newproc//To Delete a stored procedure

(4) Declare variable name variable type; For variable declarations

(5) Select @age =age....//for variable assignment

Three. Characteristics of stored procedures
(1) A series of SQL statements can be executed in a single stored procedure
(2) Other stored procedures can be referenced from their own stored procedures
(3) The stored procedure runs and is saved in the server
(4) Stored procedures have security features and ownership characteristics that can be attached to their certificates
(5) Stored procedures can enforce the security of the application

Trigger

A trigger is a special kind of stored procedure that is not performed manually by an administrator, but automatically.

A better blogger for trigger analysis:
(1) http://blog.csdn.net/pengpegv5yaya/article/details/8645261 summarizes the basic syntax of triggers

(2) http://blog.csdn.net/lenotang/article/details/3329636 the process of insert,delete,update with the access payment as an example.

The basics you'll need to know when viewing a blog:

(1) SELECT * into Table1 from table where query condition

If Table1 does not exist, create a table1 table and insert data into Table1 based on query criteria

Insert into table1 select * Form table1

If table1 exists, the data queried from the table is inserted into the Table1

(2) RAISERROR effect: throws an exception

RAISERROR (' No modification during the trading day ');//Throws an exception, the reason for the transaction period is not allowed to modify

(3) Rollback transication//Transaction Revocation fallback

SQL Server stored procedures and triggers