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

[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.