[Data Center reconstruction]-view, trigger, use of stored procedure, trigger Stored Procedure

Source: Internet
Author: User

[Data Center reconstruction]-view, trigger, use of stored procedure, trigger Stored Procedure

In the data room charging system, it is very troublesome to write multiple SQL statements to query multiple tables. To facilitate code writing, code decoupling and, we reference views, triggers, and stored procedures.


1. What is it?

View

Virtual tables constructed from several basic tables and other views. The view itself does not store actual data, but only stores a Select statement and metadata of the involved table.


Trigger

For special stored procedures, this mechanism is triggered by transactions, rather than calling stored procedures.



Stored Procedure
A set of SQL statement sets and process control statement compiling modules that complete specific functions. The stored procedure is compiled and stored in the database on the database server. It can be called during use.


2. Why?
View
(1) provides an interface for user access. users do not have to know what the underlying table structure is. When the table changes, they only need to change the view statement, but the client does not want to change it, reducing coupling.

(2) Enhanced security. Users can only view the data provided by the view, while others cannot.

(3) Simplified Database Access



Stored Procedure

(1) improve the running speed and call the stored procedure much faster than executing an SQL statement.
(2) You only need to compile the SQL statement once.
(3) it can be called repeatedly, reducing the workload of programming.
(4) enhanced SQL functions and flexibility to complete complex building and judgment and complex operations


Trigger
(1) cascade operations through related tables in the database.
(2) Automatic Execution
(3) triggers can be forced to use more complex constraints than the constraints defined by the CHECK constraint. (This is not quite understandable)
......


3. How to use it?

1. After creating a view, it is used the same as using a table.


Create View <View Name> (<list sequence>)
AS <SELECT query statement>


2. Stored Procedure


For example, in the charging system of the data center, you must add a recharge record in the recharge table and update the card balance in the card table.





3. triggers


For example, in the data room charging system, when performing the return operation, you must update a record in the return table and delete the corresponding card in the card table.


4. Summarize the above two figures from the master's blog. The view has been used, and triggers and stored procedures have not yet been used. Although triggers and stored procedures have so many advantages, we should use them with caution and refuse to abuse them.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.