Talking about triggers and stored procedures, talking about triggers

Talking about triggers and stored procedures, talking about triggers

 

When I first came to know SQL databases last year, I came into contact with two concepts. However, there is almost nothing to do with the role of these two siblings. I slowly went through the data center charge again and again, and made a face again and again with them. Then, I recently knocked on the niuyun news publishing system. Niu was reluctant to write all the SQL statements into the storage process, all slightly complex multi-Table operations must use triggers, and only after years of dealing with the database, they should have the same fate-they can't escape, so let's have a good talk.

 

WHAT:

 

Trigger: A stored procedure that is triggered by an event when a basic table is modified. (Passive)

It ensures the integrity of the reference and complete data rows that cannot be guaranteed by the primary key and foreign key.

 

Stored Procedure: a database object that encapsulates statements responsible for different functions for repeated calls. (Active)

 

WHY:

 

Trigger:

1: implement data integrity that is more complex than constraints

2: return custom error messages

3: Implement continuous modification of multiple tables

Stored Procedure:

1: Statement encapsulation, which can be called multiple times. just like Is Object-Oriented.

2: Faster execution (the first execution of the stored procedure is analyzed and optimized)

3: ensure data security (the system administrator can restrict the permissions of stored procedures)

 

Personal Understanding:

1: A trigger is a passive execution process. It is more like a protection mechanism. When some data in a database of mine is modified, the trigger I wrote in advance is triggered like this, then, the data in other tables is modified reasonably through the primary and foreign key constraints to satisfy the integrity of the entire data. The storage process is a full range of active and capable users, taking the initiative to classify and encapsulate SQL statements, just like the method we encapsulate when the data center charges fees, after I write it, you can directly call it with similar requirements.

 

2: triggers and stored procedures are operations and modifications at the database level. It can be said that they basically have nothing to do with the software code we want to implement. This is also in line with the stratified thinking, it is also conducive to the maintenance and modification of later systems. If you want to change the function block, you only need to modify the stored procedure or trigger in the database, instead of opening a large segment of code to modify the SQL statement.

 

3: both protect data from different aspects. triggers can protect data integrity, while stored procedures maintain data security through permission management.