Let's look at the database -- (3) triggers and database triggers.

Let's look at the database -- (3) triggers and database triggers.
Concept:

A trigger, as its name implies, is triggered by an event. For example, when we operate a table, it will be activated for execution.

When talking about triggers, another key point is to "maintain data integrity ". What does it mean? For example, the business requirement is that when we cancel a card number, we also delete the card's recharge and computer information. At this time, if one operation is performed, it will be: deregister the card -- delete the card's recharge information -- delete the card's computer information (two delete operations in no particular order ). The disadvantage of doing so is that we can easily omit one of the steps and the business is not complete. After a trigger is used, the trigger is activated when the card is canceled to perform the delete operation.

The advantage of using a trigger is thatStrengthen data integrity constraints and comply with business rules.

Type:
DML triggers

Common

Under the DML trigger, the trigger will automatically execute the add, delete, modify, and query operations. DML triggers are mainly used to enforce business rules, expand SQL Server constraints, and default values.

Trigger can make up for the defect that the constraints only constrain data in the same table.

DDL trigger

Main role: Review and specification.

We mainly use it to record the modification process of the database and restrict the programmer's modifications to the database, such as disallowing the deletion of some specified tables.

Logon trigger

Apparently, the logon event is responded.

The logon trigger is triggered after the logon authentication phase is complete and the user session is established. Therefore, all messages (such as error messages and messages from the PRINT statement) sent from within the trigger are usually sent to the SQL Server Error Log. If the authentication fails, the logon trigger is not triggered.

Format:

Create trigger <TRIGGER Name>

[BEFORE | AFTER] <trigger event> ON <Table Name>

For each [ROW | STATEMENT]

[WHEN <trigger condition>]

<Trigger action>

Note:

The only difference between a trigger and a stored procedure is that a trigger is automatically triggered when a user executes a Transact-SQL statement instead of calling an EXECUTE statement.

Trigger itself is not at fault, but because of our misuse, it will cause difficulties in database and application maintenance. If we rely too much on triggers, it will inevitably affect the database structure and increase the complexity of maintenance.

Reasonable trigger writing requires a lot of designers and writers. Therefore, you must fully analyze relevant services and fully understand the working principles of triggers. That is to say, the trigger should be fully considered in the business and be technically the best so as not to affect the business and performance.

Trigger implementation functions are relatively hidden and not easy to be noticed, which brings difficulties to later maintenance.

Triggers and constraints:

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.

 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.

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.

Where can the SQL database trigger be viewed?

In the data browser, there are tables below.

How to view all triggers in the SQL database?

1) view the trigger type in the table:
Sp_helptrigger: Type of the DML trigger defined for the specified table of the current database. Sp_helptrigger cannot be used for DDL triggers.
Example: EXEC sp_helptrigger 'table name'

2) view the trigger information:
Sp_help: reports information about database objects (all objects listed in sys. sysobjects Compatibility View), user-defined data types, or certain data types.
Example: EXEC sp_help 'trigger name'

3) display trigger definition:
Sp_helptext: displays the text of rules, default values, unencrypted stored procedures, user-defined functions, triggers, or views.
Example: EXEC sp_helptext 'trigger name'

4) view all triggers in the current database:
Query Script: SELECT * FROM Sysobjects WHERE xtype = 'tr'

5) view all the triggers in the current database and the corresponding tables:
Query Script: SELECT tb2.name AS tableName, tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent _ obj = tb2.id WHERE tb1.type = 'tr'