Shulou(Shulou.com)05/31 Report--

This article mainly analyzes how to analyze the relevant knowledge points of triggers in sql, the content is detailed and easy to understand, the operation details are reasonable, and has a certain reference value. If you are interested, you might as well follow the editor and learn more about "how to analyze triggers in sql".

one。 What's the trigger?

Trigger (trigger) is a method that SQL server provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by the program, nor started manually, but triggered by events. For example, when operating on a table (insert,delete, update), it is activated to execute, and the query has no trigger. Triggers are often used to strengthen data integrity constraints and business rules. Of course, we need to know that stored procedures do not return a return, while triggers have (return).

two。 What can a trigger do?

Triggers can query other tables and can contain complex SQL statements. They are mainly used to enforce compliance with complex business rules or requirements. For example, you can control whether new orders are allowed to be inserted based on the customer's current account status. This is Baidu encyclopedia's answer, see do not understand, then I think the trigger is like an event in javaScript, click event or mouse move in and out of the event, that is, we do not need to manually start it, just set the event, when he executes the beginning of these events according to a certain behavior (here is the trigger), with the trigger we can execute the first sql statement The trigger also executes the second statement (of course, the classification of triggers is involved here)

Third, the classification of triggers

DDL trigger

It is a new trigger added by Sql Server2005, which is mainly used to audit and standardize the operation of tables, triggers, views and other structures in the database. For example, in modifying tables, modifying columns, adding tables, adding columns, and so on. It is executed when the database structure changes, and we mainly use it to record the modification process of the database and to restrict programmers' changes to the database, such as not allowing certain specified tables to be deleted.

DML trigger

When the data in the table in the database changes, including any insert,update,delete operation, if we write a corresponding DML trigger to the table, the trigger executes automatically. The main role of DML triggers is to enforce industry rules, and to extend Sql Server constraints, default values, and so on. Because we know that constraints can only constrain data in the same table, while triggers can execute arbitrary Sql commands.

Example: create a trigger to add the deleted record to the delete log table when the employee table deletes a record

Alternative trigger

INSTEAD OF triggers, also known as alternative triggers, are used to perform an alternative operation instead of the operation that triggers the event

Do. For example: the INSTEAD OF trigger for the INSERT event, which is triggered by the INSERT statement, when INSERT appears

Statement, the statement is not executed, but the statement defined in the INSTEAD OF trigger is executed.

To create an INSTEAD OF trigger, you need to pay attention to the following:

Can only be created on a view, and the view does not specify a WITH CHECK OPTION option.

BEFORE or AFTER options cannot be specified. The FOR EACH ROW child is optional, that is, INSTEAD OF triggers can only be fired at the row level, or can only be row-level triggers, and there is no need to specify.

There is no need to create an INSTEAD OF trigger on a view for a table, just create a DML trigger.

System event trigger:

Login trigger:

Example: create login and exit trigger

Fourth, the extreme role of triggers.

Data can be forcibly verified or converted before being written to the data table.

When an error occurs in the trigger, the result of the change is undone.

Some database management systems can use triggers for data definition language (DDL), called DDL triggers.

The abnormal instruction (INSTEAD OF) can be replaced according to the specific situation.

five。 Application of trigger

1. Ensure the security of the database

Users' actions can be limited based on time, such as not allowing database data to be modified after work and holidays.

User operations can be restricted based on data in the database, for example, prices are not allowed to rise by more than 10% at a time.

two。 Implement complex security authorization

Using triggers to control the security of entities, permissions can be applied to the values of various databases.

3. Provide complex audit functions

Audit the statements that the user manipulates the database.

Write the user's updates to the database to the audit table.

4. Maintain synchronization tables between different databases

Snapshots can be used to replicate data between different databases, but some systems require two database data

Real-time synchronization requires the use of triggers to copy data from one database to another.

5. Implement complex data integrity rules

Implement non-standard data integrity checks and constraints. Triggers can create more complex restrictions than rules. It has nothing to do with the rules

Similarly, triggers can reference columns or database objects.

Provides a variable default value.

6. Implement complex non-standard database-related integrity rules

Triggers can make serial updates to the relevant tables in the database. For example, on the author_code column of the auths table

A delete trigger can cause matching rows in other tables to be deleted accordingly.

Triggers can reject or roll back changes that undermine the integrity of the relevant changes and cancel transactions that attempt to update data. When

This trigger works when you insert a foreign key that does not match its primary key.

six。 The composition of the trigger

The trigger is made up of

Trigger events (e. G. additions, deletions, changes and queries).

Trigger time (that is, before or after the occurrence of additions, deletions, changes and searches).

Trigger action (the purpose and intention after the TRIGGER is triggered)

Trigger objects (including tables, views, schemas, databases. The trigger action is performed only if a trigger event that meets the trigger condition occurs on these objects.) .

Trigger condition (a logical expression is specified by the WHEN clause. Only when the value of the expression is TRUE, the trigger is automatically executed when it encounters a trigger event, causing it to perform the trigger action.) .

Trigger frequency (indicates the number of times the action defined in the trigger is performed. Namely statement level (STATEMENT) trigger

Device and row level (ROW) triggers.)

seven。 Trigger sequence of triggers

1. Execute BEFORE statement-level triggers

2 pairs with each line affected by the statement

2.1 execute BEFORE row-level triggers

2.2 execute DML statement

2.3 execute AFTER row-level triggers

3. Execute AFTER statement-level triggers

eight。 Advantages of triggers

Excellent: triggers can cascade changes through related tables in the database, but cascading referential integrity is about

Bundles can perform these changes more efficiently. Triggers can enforce more complex constraints than those defined with CHECK constraints

Restraint. Unlike CHECK constraints, triggers can reference columns in other tables. For example, a trigger can use another

The SELECT in each table compares inserted or updated data, and performs other operations, such as modifying data or displaying user defined

Semantic error message. Triggers can also evaluate the table status before and after data modification and take countermeasures according to their differences. In a table

Multiple similar triggers (INSERT, UPDATE, or DELETE) allow multiple different countermeasures to be taken in response to the same

A modification statement.

Deficiency: trigger is powerful and can easily and reliably implement many complex functions, but it also has some disadvantages.

It is because of our abuse that it is difficult to maintain databases and applications. In database operations, we can pass through the

Relationships, triggers, stored procedures, applications, etc., to achieve data manipulation. At the same time, rules, constraints and default values are also guarantees.

An important guarantee of data integrity. If we rely too much on triggers, it is bound to affect the structure of the database and increase the number of

The complex procedure of maintenance.

nine。 Considerations for writing triggers

First of all, triggers do not accept parameters

Secondly, there can be at most 12 triggers on a table, but there can be only one trigger of the same type at the same time, the same event, and there can be no contradiction between triggers.

Of course, the more triggers in a table, the greater the impact on the performance of the table's DML operation, and the maximum trigger is 32kb.

If it is really necessary, you can first establish the procedure and then call it with the call statement in the trigger

Only DML statements can be used in the triggered execution part, not DDL statements

A transaction control statement cannot be included in a trigger because the trigger is part of the trigger statement, the trigger statement is committed, and the trigger is committed or rolled back when it is rolled back

Any procedure or function called in the body of the trigger cannot use transaction control statements.

No blob variables of long can be declared in the trigger body, and the new value new and the old value oid cannot be declared to any long and blob columns in the table.

The grammatical format and function of different types of triggers are quite different.

Triggers declare variables in a similar way to dephi, using the: = symbol to assign values, the new value new, the old value old do not forget: quotation marks.

This is the end of the introduction on "how to analyze triggers in sql". More related content can be searched for previous articles, hoping to help you answer questions and questions, please support the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.