trigger

[chù fā qì]

Terminology of database principles

open 7 entries with the same name

Collection

zero Useful+1

zero

This entry is made by Compilation and application of scientific encyclopedia entries of "Science Popularization China" to examine.

Trigger is provided by SQL server to programmer And the data analyst Data integrity It is a special stored procedure related to table events implement It is not called by a program or started manually, but triggered by an event, such as When a table is operated (insert, delete, update), it will be activated for execution. Triggers are often used to strengthen data integrity constraints and business rules. Triggers can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionaries. The trigger of SQL3 is a statement that can be automatically executed by the system to modify the database.

Triggers can query other tables and can contain complex SQL sentence. 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 according to the current account status of the customer.

Triggers can also be used to enforce referential integrity so that relationships defined between tables are preserved when rows are added, updated, or deleted in multiple tables. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in related tables. If you use a database diagram, you can create relationships between tables to automatically create foreign key constraints.

The only difference between a trigger and a stored procedure is that a trigger cannot execute an EXECUTE statement call, but automatically triggers execution when the user executes a Transact SQL statement.

In addition, trigger is the basic unit circuit of logic circuit, with memory function, which can be used for binary data storage, memory information, etc.

Chinese name: trigger
Foreign name: trigger/Flip-Flop
Introduction: Special events related to table events stored procedure

General type: DML trigger, DDL trigger, etc
Role: Force data verification or conversion before writing to the data table
Category: DDL trigger, DML trigger, login trigger

catalog

Function of trigger

Announce

edit

Triggers have the following functions:

You can force verification or conversion of data before writing to the data table.
When an error occurs in the trigger, the result of the job transfer will be revoked.
Some database management systems can use triggers for Data Definition Language (DDL), called DDL triggers.
The changed instruction (INSTEAD OF) can be replaced according to specific conditions.

classification

Announce

edit

^[1] SQL Server includes three general types of triggers: DML triggers, DDL triggers, and login triggers.

DML trigger

When the data in the table in the database changes, including any operations such as insert, update, and delete, if we write the corresponding DML trigger to the table, the trigger will be automatically executed. DML triggers are mainly used to enforce business rules and extend Sql Server constraints and default values. Because we know that constraints can only constrain data in the same table, and triggers can execute any Sql command.

DDL trigger

It is a new trigger of Sql Server 2005, which is mainly used to audit and standardize the structural operations on tables, triggers, views, etc. in the database. For example, in modifying tables, modifying columns, adding tables, adding columns, etc. It's in database structure When changes occur, we mainly use it to record the database modification process and limit the programmer's modification of the database, such as not allowing to delete some specified tables.

logon trigger

Login triggers fire stored procedures in response to LOGIN events. This event is raised when a user session is established with a SQL Server instance. The login trigger fires after the authentication phase of the login is complete and before the user session is actually established. Therefore, all messages from within the trigger that usually reach the user, such as error messages and messages from the PRINT statement, are delivered to the SQL Server error log. If authentication fails, the login trigger will not fire.

SQL syntax

Announce

edit

DELIMITER |

CREATE TRIGGER `<databaseName>`.`<triggerName>`

< [ BEFORE | AFTER ] > < [ INSERT | UPDATE | DELETE ] >

ON [dbo]<tableName>//dbo represents the owner of the table

FOR EACH ROW

BEGIN

--do something

END |

advantage

Announce

edit

Triggers can cascade changes through related tables in the database, but these changes can be executed more effectively through cascading referential integrity constraints. Triggers can enforce constraints that are more complex than those defined by CHECK constraints. Unlike the CHECK constraint, triggers can reference columns in other tables. For example, triggers can use SELECT in another table to compare inserted or updated data, and perform other operations, such as modifying data or displaying user-defined error messages. Triggers can also evaluate the table status before and after data modification, and take countermeasures according to the differences. Multiple triggers of the same type (INSERT, UPDATE, or DELETE) in a table allow multiple different actions to be taken in response to the same modification statement.

Triggers and constraints

Announce

edit

Constraints and triggers have their own advantages in special cases. The main benefit of triggers is that they can contain complex processing logic using Transact SQL code. Therefore, triggers can support all functions of constraints; However, it is not always the best method in terms of the functions provided. Entity integrity should always be enforced at the lowest level through indexes, which are either part of PRIMARY KEY and UNIQUE constraints or created independently of constraints. Assuming that the function can meet the functional requirements of the application, domain integrity should be enforced through the CHECK constraint, and referential integrity (RI) should be enforced through the FOREIGN KEY constraint. Triggers are extremely useful when the functionality supported by constraints does not meet the functional requirements of the application.

For example, unless cascade reference operation is defined in the REFERENCES clause, the FOREIGN KEY constraint can only verify the column value with a value that exactly matches the value in another column.

The CHECK constraint can only validate column values against a logical expression or another column in the same table. If your application requires column values to be validated against columns in another table, you must use triggers. Constraints can only pass error messages through standard system error messages. If your application requires (or benefits from) custom information and more sophisticated error handling, you must use triggers.

Triggers can be cascaded through related tables in the database; However, these changes can be performed more efficiently by cascading referential integrity constraints. Triggers can suppress or roll back changes that violate referential integrity, thereby canceling the attempted data modification. This type of trigger may work when a foreign key is changed and the new value does not match the primary key. 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 title.title_id. However, FOREIGN KEY is usually used to achieve 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 constraint is broken, the INSTEAD OF trigger operation is rolled back and the AFTER trigger is not executed.

Can triggers be created on the view in SQL Server ™ In online books, it is not said that triggers cannot be created on the view, and the syntax explanation indicates that the view can be created after CREATE TRIGGER is ON. However, this does not seem to be the case, and many experts also say that triggers cannot be created on views. I also made a special test. It is true that no trigger can be created on a normal view or an indexed view. Is it true?

But there is nothing wrong with it: when a trigger is created on a temporary table or system table, it will be rejected.

Deeply understand that the FOR keyword of the FOR CREATE TRIGGER statement can be followed by one or more of INSERT, UPDATE, and DELETE, which means that triggers will not be triggered in other cases, including SELECT TRUNCATE、WRITETEXT、UPDATETEXT。

Related content An interesting application. We see that many registration systems cannot change the user name after registration, but this is mostly determined by the application. If you directly open the database table to change it, you can also change its user name, The user name cannot be changed ingeniously by using rollback in the trigger... When the internal statement of the detail trigger fails... In this case, the previous data change operation will be invalid. For example, if the trigger is triggered when inserting data into a table, and a runtime error occurs inside the trigger, an error value will be returned and the data insertion just now will be rejected. Most T-SQL statements can be used in statement triggers that cannot be used in triggers, but the following statements cannot be used in triggers.

CREATE statements, such as CREATE DATABASE, CREATE TABLE, CREATE INDEX, etc.

ALTER statements, such as ALTER DATABASE, ALTER TABLE, ALTER INDEX, etc.

DROP statements, such as DROP DATABASE, DROP TABLE, DROP INDEX, etc.

DISK statements, such as DISK INIT and DISK RESIZE.

LOAD statements, such as LOAD DATABASE and LOAD LOG.

RESTORE statements, such as RESTORE DATABASE and RESTORE LOG.

RECONFIGURE

TRUNCATE TABLE statement cannot be used in the trigger of sybase!

Use trigger with caution

Announce

edit

Triggers are powerful and can easily and reliably implement many complex functions. Why use them carefully. The trigger itself is not at fault, but our misuse will cause difficulties in maintaining databases and applications. In database operation, we can implement data operation through relationships, triggers, stored procedures, applications, etc... At the same time, rules, constraints Default Is also a guarantee Data integrity The important guarantee of. If we rely too much on triggers, it will inevitably affect the database structure and increase the complexity of maintenance degree 。

Sqlserver Example

Announce

edit

insert

create trigger tri_insert on student for insert as declare @student_idchar(10) select @student_id=s.student_id from students inner join insertedion s.student_id=i.student_id if @student_id='0000000001' begin Raiserror ('The student number of 1 cannot be inserted! ', 16,8) rollbacktran end go

update

create trigger tri_update on student for update as if update(student_id) begin Raiserror ('Student ID cannot be modified! ', 16,8) rollbacktran end go

delete

create trigger tri_delete on student for delete as declare @student_idvarchar(10) select @student_id=student_id from deleted if @student_id='admin' begin Raiserror ('error ', 16, 8) rollbacktran end

Novice on the road

Growth task Getting Started with Editing Edit Rule Edit by myself

I have questions

Content query Online Service Official post bar Feedback

Complaints and suggestions

Report bad information Failed to appeal through entries Complaint of infringement information Blocking query and unblocking

Jinggong Network Anbei No. 1100000200001