Database Design of Data room Charging System (2)

The database design overview has been started for a long time. However, it is only completed today and I cannot stand it, but it can also be accepted because it is concentrated in the database design for a short time. In the next day, it's time to start the UML drawing stage. At this stage, we should repeat the entire process and apply the knowledge of the last three layers. Let's talk about database design.

Database design is an iterative process. At this stage, there are two static databases built just before, and small-scale modifications are not required, I believe that there are still changes when connecting to the application.

The self-designed database uses some basic technologies to implement the imperceptible functions of data deletion and real deletion. during the design process, it encountered a misunderstanding, it is to confuse the final application with the database management program. The final application is controlled by the user level of the Database Management Program, and the application itself, while the database management program is only controlled by the user level. During the database design stage, I would like to thank Li Baoqiang and Xiong Qingfeng for their help. The following are some technologies used by databases.

Trigger

The trigger type is delete In instead. Because the database has many foreign key constraints, you need to know the relationship between the table and the table in the database when deleting the database records, to solve this problem, create these triggers and use cascade and cursors to simplify data deletion for users in the database management system.

Ti_operator_info trigger, triggered when the operator deletes the previous time of the operator.

Use [charge] goset ansi_nulls ongoset quoted_identifier ongoif exists (select * From DBO. sysobjects where id = object_id (n' [DBO]. [ti_operator_info] ') and objectproperty (ID, N 'istrigger') = 1) Drop trigger triggers trigger ti_operator_info on operator_infoinstead of deleteas declare @ operator_id char (20) declare @ operator_login_id intbegin -- get the ID of the operator to be deleted select @ operator_id = operator_id from deleted -- declare a cursor declare cur cursor for select distinct from cursor where operator_id = @ operator_id -- open the cursor cur -- use the cursor fetch next from cur into @ operator_login_id -- delete all computer record information of the operator while @ fetch_status = 0 begin Delete from role where operator_id = @ operator_login_id fetch next from cur @ operator_login_id end -- delete operator information again Delete from operator_info where operator_id = @ operator_id end

Note: because a user ID may correspond to multiple computer records, you need to use a cursor to delete all these computer records one by one. Because only one trigger is attached here, I can't see the meaning of cascading deletion. Here I will describe some details. to delete an operator user, you must first delete its computer record information. To delete its computer record information, you must first Delete the operation information on the machine.

Stored Procedure

The stored procedure is mainly used by the final application, and some operations of the operator have corresponding information records. Therefore, a corresponding stored procedure is established for the purpose.

Proc_addoperator stored procedure, which operator adds some T-SQL statements called when Operator

-- Determine whether there is a stored procedure with the same name if (object_id ('proc _ addoperator', 'P') is not null) drop proc proc_addoperatorgo -- create proc proc_addoperator (-- operator's computer record number @ operator_login_id int, -- operator information @ operator_id varchar (20), @ operator_key varchar (20 ), @ operator_name varchar (20), @ operator_sex varchar (20), @ operator_level varchar (20 )) as begin -- do not return the number of rows affected by the T-SQL statement set nocount on -- when a statement in the transaction fails, the transaction rolls back to the set xact_abort on begin transaction state at the beginning of the transaction -- starts the transaction -- writes data to the registration record table first, then you can write data to the operator information table -- operator_register_id is an auto-incrementing insert into partition (operator_login_id, operator_user_id, region, region) values (@ operator_login_id, @ operator_id, convert (varchar (10), getdate (), 120), convert (varchar (8), getdate (), 108) -- insert into operator_info (operator_id, operator_key, operator_level, operator_name, region, operator_state) values (@ operator_id, @ operator_key, @ operator_level, @ operator_name, @ operator_sex, 'offline') Commit

The proc_daysum trigger is used to summarize the general situation of today.

Description: The proc_daysum stored procedure is used to insert data into daysum. If (object_id ('proc _ daysum', 'P') is not null) drop proc proc_daysumgocreate proc proc_daysum (-- operator computer record ID @ operator_login_id INT) as declare @ daysum_date date declare @ daysum_loginno int declare @ brief real declare @ brief int declare @ brief real declare @ every real begin -- get today's date set @ daysum_date = convert (varchar (10 ), getdate (), 120) -- get the total number of people on the machine today select @ daysum_loginno = sum (user_login_id) from region where user_login_date = @ daysum_date -- get the total number of registered users today select @ daysum_registerno = sum) from region where region = @ daysum_date -- get the total number of cards returned today select @ region = sum (card_return_id) from region where card_return_date = @ daysum_date -- get the total amount of cards returned today select @ daysum_returnmoney = sum) from region where card_return_date = @ daysum_date -- select @ region = sum (card_recharge_id) from region where card_recharge_date = @ daysum_date -- get today's Recharge Amount select @ region = sum (card_recharge_money) from region where card_recharge_date = @ daysum_date -- Obtain the profit amount for today select @ daysum_makemoney = sum (user_cost) from user_login_record, Bill where user_login_date = @ daysum_date and Bill. bill_id = user_login_record. bill_id -- insert data to daysum if exists (select * From daysum where daysum_date = @ daysum_date) delete from daysum where daysum_date = @ daysum_date insert into daysum (daysum_date, daily, daysum_loginno, daily, usage, usage, daysum_returnmoney, usage, billing) values (@ daysum_date, @ operator_login_id, @ daysum_loginno, @ override, @ daysum_returnmoney, @ override, @ override) endgo

View

Get the total summary from the daysum table

if exists(select TABLE_NAME from INFORMATION_SCHEMA .VIEWS where TABLE_NAME =N'View_SumAll')drop view View_SumAllgocreate view View_SumAll(Sum_LoginNO,Sum_RegisterNO,Sum_ReturnNO,Sum_ReChargeNO,Sum_ReturnMoney,Sum_ReChargeMoney,Sum_MakeMoney)asSelect SUM(DaySum_LoginNO),SUM(DaySum_RegisterNO),SUM(DaySum_ReturnNO),SUM(DaySum_ReChargeNO),SUM(DaySum_ReturnMoney),SUM(DaySum_ReChargeMoney),SUM(DaySum_MakeMoney) from DaySum

Note: The creation of a view is mainly used to create a summary.

Summary

At this stage, the trigger and stored procedure are created. There are more than a dozen, but only one is posted. If you are interested, you can leave a message and I will give it to you. In general, this blog is not very successful. There are many things to talk about, but few are written.