15-07-22 database--Stored procedures, triggers

One, storage

Running in the MyDB database

SELECT * FROM Fruitselect * from Loginselect * from Orderdetailsselect * to Ordersselect * FROM info SELECT * FROM Familygo-Stored procedures, to achieve the user purchase of fruit operations, to determine the relationship between the purchase quantity and inventory, purchase the relationship between the total price and the balance, if the purchase successfully modified 4 table CREATE PROCEDURE Buyfruit-Create a store--Define the parameters, The parameters are separated by commas @uid varchar (,--user name @fcode varchar (),--to buy the fruit number @sl INT--The total amount of fruit to buy as begin declare @kc int, @price float--define the variable name and data type, stock and unit price select @kc =numbers, @price =price from Fruit where Ids = @fcode-Check inventory and unit price (second method of variable assignment, find value for assignment)--Determine if the quantity purchased Greater than inventory if @sl > @kc begin print ' Not enough! ' End else--can buy begin declare @yue float--Define the balance variable select @yue = account from Login where [email protected] assigns the balance to determine if the balance is greater than the quantity purchased * unit price if @yue &  gt;= @price * @sl begin--after purchase change table information Update Fruit SET numbers = [email protected] where Ids [email protected] Changes the quantity of inventory-the number of purchases update Login Set account = account-@price* @sl where Username= @uid--Change deposit Amount: Existing amount-Purchase quantity * unit Price declare @sj int--define a variable random set @sj = CAST (RAND () *10000 as int)--randomly generate an order number and randomly generate a number of 0-1 inserts into Orders values (@sj, @uid, GETDATE ())--g                        Etdate get time INSERT into OrderDetails values (@sj, @fcode, @sl) end else--balance Insufficient begin print ' Insufficient balance! ' End end Endgodeclare @s intexec @s = Buyfruit ' Zhangsan ', ' k001 ', 1--Call stored procedure, PRI NT @s

--Delete stored procedures
drop proc Buyfriut

Second, Trigger

--triggers, special stored procedures, execution time and stored procedures are not the same, stored procedures are executed at the time of invocation, triggers are executed at the time of the operation, equivalent to events in C #

Triggers are special types of stored procedures that can automatically take effect when a language event is executed. SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

DDL triggers are invoked when data definition language (DDL) events occur in the server or database. The logon trigger fires the stored procedure in response to a logon event. This event is raised when a user session is established with an instance of SQL Server.

DML triggers are invoked when data manipulation language (DML) events occur in the database. DML events include INSERT statements, UPDATE statements, or DELETE statements that modify data in a specified table or view. DML triggers can query other tables and can also contain complex Transact-SQL statements. Treats the trigger and the statement that triggered it as a single transaction that can be rolled back within the trigger. If an error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back.

There are two main types of DML triggers, DML triggers: After (for), INSTEAD of triggers, and DML triggers using deleted and inserted logic (concept) tables. They are structurally similar to the table that defines the trigger, which is the table on which the user action was attempted. The deleted and inserted tables save old or new values for rows that may be changed by the user.

• For insert operations, inserted retains new records, deleted no records
• For delete operations, inserted no records, deleted retains deleted records
• For the update operation, inserted retains the modified record, deleted retains the pre-modified record

One, after delete execute for (after) delete

--Build Loginone, Biandong database select * FROM Loginoneselect * from biandonggo--CREATE TRIGGER trigger Tr_loginone_delete--create Trigger create trigger on Loginone--on is followed by execution of a for delete to that table--delete-Execute--after Delete--executes--instead of delete after delete--Overrides execution Asbegin    DECLARE @uid varchar, @name varchar    select @uid =username, @name =name from deleted--find 2 data from delect Delete table, data deleted    INSERT into Biandong values (@uid, @name, ' delete ') endgo--the trigger executes the delete from Loginone where username= ' when the table Loginnoe is deleted aaa

Ii. Alternative execution instead of

--instead of Trigger-delete the data inside the STUDENT with another two alternative, first delete from the table and then delete the main table create trigger Tr_student_deleteon STUDENT instead of Deleteasbegin    declare @sno varchar ()    select @sno =sno from deleted    Delete from score where [email protected ]    Delete from student where [email protected]end--execute DELETE trigger delete from student where sno= ' 101 ' select * FROM Studentsel ECT * FROM Scoreselect * from Courseselect * from teacher

15-07-22 database--Stored procedures, triggers