Stored Procedure trigger Constraints

Definition (cannot be assigned when defining): declare @ name Type

Value assignment: Set (or select) @ name = 'value to be assigned to @ name'

Stored Procedure:

Manual call

The disadvantage is that it is not easy to migrate data (table migration during migration and data rewriting during storage)

Format:

Create procedure stored procedure name (up _ TABLE name_operation)

Row parameter (passed row parameter)

As

Begin

Definable variables (temporary row parameters)

Statement

End

Usage:

Stored Procedure name row Parameter

Example:

Alter procedure [DBO]. [buyfruit]

@ Username varchar (20 ),

@ Fruitname varchar (20 ),

@ Buycount Int = 0

As

Begin

Declare @ KC int, @ price float, @ fruitid varchar (20)

-- First find out the inventory of the fruit

Select @ fruitid = IDs, @ kc = numbers, @ price = Price from fruit where [email protected]

-- Purchase based on the relationship between the quantity purchased and the inventory

If @ buycount <@ KC

Begin

Declare @ money decimal (18, 2)

Select @ money = Account from login where [email protected] -- find the account balance based on the user name

If (@ money> @ price * @ buycount)

Begin

Update login set [email protected] * @ buycount where [email protected]

Update fruit set numbers = [email protected] Where [email protected]

Declare @ ordercode varchar (50)

Set @ ordercode = 'O' + Cast (getdate () as varchar (50 ))

Insert into orders values (@ ordercode, @ username, getdate ())

Insert into orderdetails values (@ ordercode, @ fruitid, @ buycount)

End

Else

Begin

Print 'Insufficient balance'

End

End

Else

Begin

Print 'inventory insufficiency'

End

End

 

Trigger:

Searchable, readable, and unchangeable

The system automatically writes data, including inserted and deleted temporary tables.

Inserted stores any new information after change

Deleted stores the last deletion Information

Description of the table content in the extended attributes in the table properties to get the results at a glance

When deleting a primary key in a primary table, if the data in the table still has a value related to the primary key, the operation fails because it breaks the integrity of the reference.

By default, the primary key can be deleted only after the table data is deleted,

Cascade: sets cascade from the table relationship in the upper left corner of the table design.

Format:

Create trigger name (up _ TABLE name_operation)

On Table Name

For (or insert of) Operations

As

Statement

Go

Example:

Create trigger tr_student_delete

On student

For Delete

As

Declare @ No varchar (3), @ name varchar (4)

Select @ No = SnO, @ name = sname from deteled

Insert into biandong values (@ No, @ name, '2013 ')

Go

Select * from student

Transaction:

Begin Tran (or transaction) -- start the transaction

Commit -- submit

Rollback -- roll back the transaction

Transaction Features: atomicity)

Consistency)

Isolation)

D durability)

@ Error is the condition for judging whether the transaction is correct. The value of the error is 0 and the value of the error is not 0.

Example:

If @ error <> 0

Rollback

Else

Commit

Example:

Begin tran

Begin try

Statement

Commit

End try

Begin catch

Rollback

End catch

 

 

Understanding pessimistic and optimistic locks

Stored Procedure trigger Constraints