What are stored procedures and triggers, and stored procedure triggers?

What are stored procedures and triggers, and stored procedure triggers?

The simplest way is to define a series of operations in SQL server. You only need to call them to complete the corresponding operations.
========================================================== ==============================
Stored Procedure Definition:
Store common or complex tasks in advance with SQL statements and a specified name, to enable the database to provide services with the same functions as the predefined stored procedure, you only need to call execute to automatically complete the command.

What is the difference between a stored procedure and a general SQL statement?
Advantages of stored procedures:
1. the stored procedure is compiled only when it is created. You do not need to re-compile the stored procedure every time you execute it. Generally, the SQL statement is compiled every time it is executed, therefore, using stored procedures can speed up database execution.
2. when performing complex operations on the database (for example, performing Update, Insert, Query, and Delete operations on multiple tables ), this complex operation can be encapsulated in a stored procedure and used together with the transaction processing provided by the database.
3. stored procedures can be reused to reduce the workload of database developers.
4. High security. You can set that only one user has the right to use the specified stored procedure.

Types of stored procedures:
1. system stored procedure: starts with sp _. It is used to set the system, obtain information, and manage the system,
For example, sp_help is used to obtain information about the specified object.
2. The extended stored procedure starts with XP _ and is used to call the functions provided by the operating system.
Exec master .. xp_mongoshell 'Ping 10.8.16.1'
3. User-Defined stored procedures, which we refer to as stored procedures
========================================================== ==============================
A trigger is a special stored procedure. Its execution is not called by a program, nor is it manually started, but triggered by an event, for example, when you perform operations (insert, delete, update) on a table, it is activated for execution. Triggers are often used to enhance data integrity constraints and business rules. The trigger can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionary.

Triggers can query other tables and contain complex SQL statements. They are mainly used to force complex business rules or requirements. For example, you can control whether to insert a new order based on the current account status of the customer.

Triggers can also be used to force reference integrity so that the relationships defined between these tables are retained when rows are added, updated, or deleted in multiple tables. However, the best way to force reference integrity is to define the primary key and foreign key constraints in the relevant table. If you use a database relationship diagram, you can create a relationship between tables to automatically create a foreign key constraint.

　　SQL syntax for creating a trigger

DELIMITER |

Create trigger ''.''



ON

FOR EACH ROW

BEGIN

-- Do something

END |

　　Advantages of triggers

Triggers can implement cascade changes through relevant tables in the database. However, they can be executed more effectively through cascading integrity constraints. A trigger can force more complex constraints than those defined by the CHECK constraint. Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use the SELECT statement in another table to compare the inserted or updated data and perform other operations, such as modifying data or displaying user-defined error information. The trigger can also evaluate the table status before and after data modification and take countermeasures based on the difference. Multiple similar triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different countermeasures to respond to the same modification statement.

　　Comparison triggers and constraints

Constraints and triggers have their own advantages in special circumstances. The main benefit of triggers is that they can contain complex processing logic using Transact-SQL code. Therefore, a trigger can support all the functions of the constraint, but it is not always the best method for the given functions. Entity integrity should always be enforced at the lowest level through indexes, which are either part of the primary key and UNIQUE constraints, or independently created outside of the constraints. If the function can meet the functional requirements of the application, the domain integrity should be enforced by the CHECK constraint, and the reference integrity (RI) should be enforced by the foreign key constraint. Triggers are extremely useful when the functions supported by constraints cannot meet the functional requirements of applications.

For example, unless the REFERENCES clause defines a cascade reference operation, the foreign key constraint can only verify the column value by a value that exactly matches the value in the other column.

The CHECK constraint can only verify the column value based on a logical expression or another column in the same table. If the application requires that the column value be verified based on the columns in another table, the trigger must be used.

Constraints can only pass error messages through standard system error messages. If the application requires (or can benefit from) custom information and more complex error handling, the trigger must be used.

Triggers can implement cascade changes through relevant tables in the database. However, they can be executed more effectively through cascading integrity constraints.

The trigger can disable or roll back the changes that violate the integrity of the reference to cancel the data modification. When the foreign key is changed and the new value does not match the primary key, this type of trigger may function. For example, you can create an insert trigger on titleauthor. title_id to roll back an insert when the new value does not match a value in titles. title_id. However, foreign key is usually used for this purpose.

If there are constraints on the trigger table, check these constraints AFTER the instead of trigger is executed but before the AFTER trigger is executed. If the constraints are damaged, roll back the instead of trigger operation without executing the AFTER trigger.

Whether a trigger can be created in a view in SQL Server books online does not mean that the trigger cannot be created in the view, and the syntax explanation indicates:

After the create trigger on, it can be a view. However, this does not seem to be the case. Many experts also say that triggers cannot be created on The View. I also did a test. Indeed, neither a common view nor an index view can create a trigger on it. Is that true? Click details, but it is understandable that a trigger is rejected when it is created in a temporary table or system table. A deep understanding of the FOR keyword of the for create trigger statement can be followed by one or more of the INSERT, UPDATE, and DELETE keywords. That is to say, the TRIGGER will not be triggered in other cases, including SELECT, TRUNCATE, WRITETEXT, and UPDATETEXT. The related content is an interesting application. We can see that many registration systems cannot change the user name after registration, but most of these are determined by the application. If you directly open the database table to make changes, you can also change the user name, and use rollback in the trigger to cleverly achieve the inability to change the user name ...... Details: When an error occurs in the internal statement of the trigger ...... In this case, the previous data change operation will be invalid. For example, if a trigger is triggered when data is inserted in a table and a running error occurs in the trigger, an error value is returned and data insertion is rejected. Most T-SQL statements cannot be used in statement triggers that are used in triggers, but the following statements cannot be used in triggers.

CREATE statement, such as create database, create table, and create index.

ALTER statements, such as alter database, alter table, and alter index.

DROP statements, such as drop database, drop table, and drop index.

DISK statement, such as disk init and disk resize.

LOAD statement, such as load database and load log.

RESTORE statements, such as restore database and restore log.

RECONFIGURE

The truncate table statement is not available in sybase triggers!

The use of trigger triggers with caution is powerful, allowing you to easily and reliably implement many complex functions. Why should we use them with caution. Trigger itself is not at fault, but because of our misuse, it will cause difficulties in database and application maintenance. In database operations, we can perform data operations through relationships, triggers, stored procedures, and applications ...... Meanwhile, rules, constraints, and default values are also an important guarantee for data integrity. If we rely too much on the trigger, it will inevitably affect the database structure and increase the maintenance of complex programs.