Trigger definition

A trigger is a special stored procedure that executes automatically when an event occurs in the database server. SQLServer allows you to create multiple triggers for any particular statement. Its execution is not invoked by a program, nor is it initiated manually, but is triggered by events that activate its execution when an operation is performed on the database.

Flip-flop classification

There are DML triggers, DDL triggers, login triggers, nested triggers, and recursive triggers.

DML trigger

define

If a user wants to edit data through a Data Manipulation Language (DML) event, a DML trigger is executed. DML events are INSERT, UPDATE, or DELETE statements for a table or view. These triggers fire when any valid event fires, regardless of whether any table rows are affected.

classification

AFTER trigger

Execute the AFTER trigger AFTER the operation of an INSERT, UPDATE, MERGE, or DELETE statement. If the constraint is violated, the AFTER trigger is never executed; Therefore, these triggers cannot be used for any processing that might prevent constraint violations. For each INSERT, UPDATE, or DELETE operation specified in the MERGE statement, the corresponding trigger is fired for each DML operation.

INSTEAD OF flip-flop

INSTEAD OF the standard action for the following trigger statement. Thus, triggers can be used to perform error or value checks on one or more columns and then perform additional actions before inserting, updating, or deleting rows. The main advantage OF the INSTEAD OF trigger is that you can enable updates to views that cannot be updated. Another advantage OF an INSTEAD OF trigger is that it allows you to write logical code that rejects some parts OF the batch while allowing other parts OF the batch to succeed.

The following table compares the functionality OF AFTER and INSTEAD OF triggers.

The CLR triggers

CLR triggers can be AFTER triggers or INSTEAD OF triggers. CLR triggers can also be DDL triggers. CLR triggers execute methods written in managed code (members of assemblies created in the.NET Framework and uploaded in Transact-SQL) rather than SQL Server stored procedures.

Application scenarios

DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements. The trigger and the statement that triggers it are treated as a single transaction that can be rolled back within the trigger. If an error is detected (for example, insufficient disk space), the entire transaction is automatically rolled back.

advantages

DML triggers are similar to constraints in that entity integrity or domain integrity can be enforced. In general, entity integrity should always be enforced at the lowest level through indexes that are either part of the PRIMARY KEY and UNIQUE constraints, or created independently of the constraints. Domain integrity should be enforced through the CHECK constraint, and referential integrity (RI) through the FOREIGN KEY constraint. DML triggers are useful when the functionality supported by constraints does not meet the functional requirements of the application.

The following list compares DML triggers and constraints and identifies when DML triggers are superior to constraints.

DML triggers cascade changes to related tables in the database; However, these changes can be performed more efficiently using cascading referential integrity constraints. Unless the REFERENCES clause defines a cascading reference operation, the FOREIGN KEY constraint can only validate a column value with a value that exactly matches the value in another column.

DML triggers prevent malicious or erroneous INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined by the CHECK constraint.

Unlike the CHECK constraint, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT in another table to compare inserted or updated data and perform other operations, such as modifying data or displaying user-defined error messages.

DML triggers can evaluate the state of the table before and after data modification and take action based on that difference.

Multiple same-type DML triggers (INSERT, UPDATE, or DELETE) in a table allow multiple different actions to be taken in response to the same change statement.

The constraint can only deliver error messages through standardized system error messages. Triggers must be used if your application needs (or could benefit from) using custom messages and more complex error handling.

DML triggers can disable or roll back changes that violate referential integrity, thereby canceling attempted data modifications. Such a trigger takes effect when a foreign key is changed and the new value does not match its primary key. However, FOREIGN KEY constraints are typically used for this purpose.

If there are constraints on the trigger table, they are checked AFTER the INSTEAD OF trigger executes but before the AFTER trigger executes. If the constraint is violated, the INSTEAD OF trigger action is rolled back and the AFTER trigger is not executed.

DDL trigger

define

DDL triggers are used to respond to various data Definition Language (DDL) events. These events correspond primarily to transact-SQL statements that begin with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS. DDL triggers can also be fired by system stored procedures that perform DDL-like operations.

classification

Transact-sql DDL trigger

A special type of Transact-SQL stored procedure used to execute one or more Transact-SQL statements in response to server – or database-wide events. For example, if a statement (such as ALTER SERVER CONFIGURATION) is executed or a TABLE is dropped using DROP TABLE, the DDL trigger fires.

CLR DDL trigger

CLR triggers execute methods written in managed code (members of assemblies created in the.NET Framework and uploaded in Transact-SQL) rather than SQL Server stored procedures.

A DDL trigger fires only after the DDL statement that triggers the DDL trigger is run. DDL triggers cannot be used as INSTEAD OF triggers. DDL triggers are not triggered for events that affect local or global temporary tables and stored procedures.

DDL triggers do not create special INSERTED and DELETED tables.

You can use the EVENTDATA function to capture information about the event that fired the DDL trigger and subsequent changes caused by the trigger.

Create multiple triggers for each DDL event.

Unlike DML triggers, DDL triggers are not scoped by schemas. Therefore, functions such as OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, and OBJECTPROPERTYEX cannot be used to query metadata about DDL triggers. Use the directory view instead.

Server-wide DDL triggers are displayed in the Triggers folder of THE SQL Server Management Studio Object Explorer. This folder is located under the Server Objects folder. Database-wide DDL triggers are displayed in the Database Triggers folder. This folder is located under the programmability folder of the corresponding database.

Application scenarios

Prevents certain changes to the database schema.

You want something to happen in the database in response to a change in the database schema.

Log database schema changes or events.

advantages

More secure

You can use XML information in triggers by using the EVENTDATA() function.

Login trigger

define

A login trigger fires the stored procedure in response to a LOGON event. This event is raised when a user session is established with an 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 (such as error messages and messages from PRINT statements) that come from within the trigger and typically reach the user are delivered to the SQL Server error log. If authentication fails, the login trigger is not fired.

Application scenarios

Login triggers can be used to audit and control Server sessions, for example by tracking login activity, limiting SQL Server login names, or limiting the number of sessions for a particular login name.

Specify the first and last triggers

Multiple triggers can be defined for LOGON events. Using the SP_setTriggerORDER system stored procedure, you can specify any of these triggers as the first or last trigger to fire for an event. SQL Server does not guarantee the execution order of the remaining triggers.

Nested trigger

If one trigger calls another trigger while performing an operation, and that trigger then calls the next trigger, a nested trigger is formed. Nested triggers are enabled at installation time, but you can disable and re-enable nested triggers using the system stored procedure SP_configure.

Matters needing attention

The nested trigger configuration option is turned on by default. A nested trigger cannot be fired twice in the same trigger transaction. Since a trigger is a transaction, if an error occurs at any level in a series of nested triggers, the whole thing is cancelled and all data is rolled back.

Recursive flip-flop

define

Recursion of a trigger means that a trigger activates the trigger again from within.

classification

There are two types of recursive triggers in SqlServer: direct and indirect.

Direct recursion: when a trigger is fired and an action is performed, that action is fired again using a trigger. Indirect recursion: A trigger fires and performs an operation that causes a trigger in another table to fire, and a second trigger updates the original table, triggering the first trigger again. By default, the recursive trigger option is disabled. A recursive trigger can recurse at most 16 levels, and if the 16th trigger in the recursion activates the 17th trigger, the result is the same as the issued ROLLBACK command, with all data rolled back.

Pros and cons of triggers

Advantages:

1. Triggers are automatic. Is activated immediately after any changes are made to the data in the table.

2. Triggers can be cascaded through related tables in the database.

3. Triggers can enforce restrictions. These restrictions are more complex than those defined with the CHECK constraint. Unlike the CHECK constraint, triggers can refer to columns in other tables.

Disadvantages:

1. Increase the complexity of the system.

2. Nested triggers are prone to deadlock.

Triggers don’t improve performance much.

4. Poor portability.

5. Occupy server resources and put pressure on the server.

6. Complex triggers are difficult to maintain.