8.2.1 The storage structure of the trigger, which is triggered by an event rather than a program or manual start. When data has special operations, the SQL statement is automatically triggered. Differences between a trigger and a stored procedure: 1. The trigger is automatically triggered and the stored procedure is called explicitly. 2. The trigger cannot use COMMIT or ROLLBACK statements. The stored procedure is acceptable. 3. The trigger cannot
8.2.1 The storage structure of the trigger, which is triggered by an event rather than a program or manual start. When data has special operations, the SQL statement is automatically triggered. Differences between a trigger and a stored procedure: 1. The trigger is automatically triggered and the stored procedure is called explicitly. 2. The trigger cannot use COMMIT or ROLLBACK statements. The stored procedure is acceptable. 3. The trigger cannot
8.2.1 trigger
Special types of storage structures are triggered by events, rather than programs or manual start. When data has special operations, the SQL statement is automatically triggered.
Differences between triggers and stored procedures:
1. The trigger is automatically triggered and the stored procedure is explicitly called.
2. The trigger cannot use COMMIT or ROLLBACK statements. The stored procedure is acceptable.
3. The trigger cannot accept parameter input, and the stored procedure is acceptable.
Two types of triggers: DML trigger and DLL trigger.
DML: a data operation trigger. It is a stored procedure executed when a data operation language event occurs on the database server. There are two types: After and Instead. The After trigger is performed After the record changes. Instead Of executes the trigger before the record changes, Instead Of the original SQL statement. That is to say, the original SQL is used to trigger this action.
DLL: a data definition trigger, which is a stored procedure triggered when a data definition statement is used.
Purpose:
1. Increased security
2. Use the trigger to record the modifications and related information.
3. Maintain the integrity constraints of replication that are impossible to achieve by declaring the constraints when the table is declared, and monitor specific events in the database.
4. Automatically complete some functions.
8.2.2 Index
Index is a mechanism for improving the access speed. It is an object created in ascending or descending order on a database table or view based on the value of a key field. When you query an index field, you can quickly perform the search operation.
Is the disk structure associated with a table or view.
Clustered index and non-clustered Index
Clustering: The physical order of rows is the same as the logical order of key values. A table can have only one clustered index. Compared with non-clustered indexes, the access speed is generally faster.
Non-clustered: The index has a pointer pointing to the data storage location, and the index items are stored in the order before the index.
8.2.3 undo and roll back
Rollback uses undo to ensure that the transaction is valid, that is, if the execution fails, it is rolled back to the status before the transaction is executed.
Withdrawal is more used to restore the database. If a sudden power failure occurs, undo is used to process unfinished transactions.
8.2.4 backup
Full backup: all information is the baseline for recovery. Not only data files, log files, storage locations, and information about all objects. Transactions and unfinished transactions during the backup process are ignored.
Differential backup: after the last full backup, operations during the differential backup process can be backed up.
Transaction Log backup: the log record after the last backup
Incremental Backup: Backup changes to the previous backup
8.2.5 cursor
You can change the database to operate by table to perform operations by row.