sql--stored procedure + trigger comparison!

One, stored procedures

A: Stored procedure: A stored procedure is a set of SQL statements that are compiled and stored in a database in order to accomplish a specific function.

You can use stored procedure names and parameters to invoke stored procedures, which avoids duplication of code and is convenient to use.

Example: The following is a stored procedure named buyfruit , the parameter is the name of the purchaser, fruit name, purchase quantity three, the role of this stored procedure is, after entering these three parameters, determine the account balance and inventory is sufficient, Enough to reduce the amount of money spent on the account balance, display the inventory minus the purchased quantity, print an order, and a breakdown.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21st 22 23 24 25 26 27 28 29 30 31 32 33 34

Create PROCEDURE Buyfruit @username varchar (20), @fruitname varchar (20), @buycount int = 0 As BEGIN declare @kc int, @price float, @fruitid varchar (20) --Find out the stock of the fruit first Select @fruitid =ids, @kc = numbers, @price =price from fruit where [email protected] --Purchase according to the relationship between purchase quantity and inventory If @buycount < @kc Begin DECLARE @money decimal (18,2) Select @money = accounts from login where [email protected]--Find account balances based on 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 ' Insufficient inventory ' End END

before purchasing the content in the database:

What is stored in the database after the purchase is successful :

Add to order and purchase details:

Two: Trigger

A trigger is a special kind of stored procedure

Triggers are executed automatically by triggering an event, and stored procedures can be called directly by the stored procedure name

The main function of the trigger is that it can realize the complex referential integrity and data consistency which cannot be guaranteed by the primary key and foreign key, plus the function of enforcing the constraint and cascade running.

About inserted and deleted temporary tables

These two tables are managed by the system, stored in memory, not stored in the database, so the user is not allowed to modify directly, is read-only, the system inserts the data into the inserted temporary table when the insert operation, and then into the database table inserted, This is deleted when the next bar is inserted, and when the delete operation is performed, the data is uploaded to the deleted table before the data is deleted, which is used to restore or record the temporary data.

The following is a trigger that is triggered when a delete is deleted, and the deleted row is inserted into the biandong table When you delete the data from the student table.

-- used to delete triggered triggers:

Create Trigger Tr_student_delete
On student
For delete--for trigger after trigger, delete after trigger
As
DECLARE @no varchar (3), @name varchar (4)
Select @no =sno, @name =sname from deleted-- uses a temporary table
INSERT into Biandong values (@no, @name, ' 100 ')
Go

-- trigger the above program when performing the deletion below .

Delete from student where sname= ' monkey '

Another is the instead of trigger, when triggered by the program inside the trigger instead of executing the action, that is, executing the trigger inside the thing

In the example below, the original three tables, which are constrained by the code in the Info table, are not able to delete a single row in info. Triggers allow you to delete lines with code p001 in three tables

Create Trigger Tr_info_delete
On info
Instead of delete--instead of triggers, when deleted replaces execution triggers

As
DECLARE @code varchar (20)
Select @code =code from deleted
Delete from family where [email protected]
Delete from work where [email protected]
Delete from info where [email protected]

Go

Instead of trigger creation complete below start trigger:

Delete from INFO where name = ' hu June '

p001 rows in three tables are deleted at this time

D. Delete Trigger:

Drop Trigger Tr_info_delete

sql--stored procedure + trigger comparison!