SQL Server Basics (triggers)

Read Catalogue

• One: Advantages of triggers
• II: The role of triggers
• Three: the classification of triggers
• Four: how triggers work
• V: Create a trigger
• VI: Manage triggers

Concept:

?? A trigger (trigger) is a method that SQL Server provides to programmers and data analysts to ensure data integrity, which is a special stored procedure related to table events, which is executed either by a program call or by a manual startup, but by an event that is triggered when a table is manipulated (insert , delete, update) activates it when it executes. Triggers are often used to enforce data integrity constraints, business rules, and so on. Triggers can be found in the dba_triggers, user_triggers data dictionary.

the difference between a trigger and a stored procedure:

?? The difference between a trigger and a stored procedure is that the trigger cannot execute the EXECUTE statement call, but instead automatically triggers execution when the user executes the Transact-SQL statement and the stored procedure requires the user, application, or trigger to be called and executed.

One: Advantages of triggers

? 1. The trigger is automatic. is activated immediately after making any changes to the data in the table.

? 2. Triggers can be cascade modified through related tables in the database.

? 3. Triggers can enforce restrictions. These restrictions are more complex than defined by check constraints. Unlike a check constraint, a trigger can reference a column in another table.

II: The role of triggers

The main function of a trigger is that it can achieve complex referential integrity and data consistency that cannot be guaranteed by primary key and foreign key, it can cascade modify related tables in the database, improve data integrity more complex than check constraints, and customize error messages. The main functions of triggers are as follows:

1. Enforcing referential integrity between databases
2. Cascade modifies all related tables in the database, automatically triggering other related operations
3. Track changes, undo or rollback illegal operations, and prevent unauthorized modification of data
4. Returns a custom error message, the constraint cannot return information, and the trigger can
5. Triggers can call more stored procedures

Three: The classification of triggers

? SQL Server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

1.DML (Data manipulation language, manipulation Language) trigger

? DML triggers are code of operations attached to specific tables or views that are performed when data manipulation language events occur in the database server. There are three types of DML triggers in SQL Server:

1. Insert trigger: Triggered when inserting data into a table;
2. Delete trigger: Triggered when data is deleted from the table;
3. UPDATE trigger: Triggered when data in a table is modified.

you should consider using DML triggers when you encounter the following scenarios:

1. Implementing cascading changes through related tables in the database
2. Additional restrictions that prevent malicious or erroneous insert, update, and delete operations, and enforce restrictions that are defined by a check constraint more complex.
3. Evaluate the status of the table before and after the data modification and take action based on that difference.

2.DDL (data definition language, Language) trigger

? DDL triggers are activated when a data definition language (primarily a statement that begins with Create,drop,alter) occurs in a server or database, and DDL triggers are used to prevent certain changes to the data schema or to record changes or event actions in the data.

3. Login Trigger

???? The logon trigger fires the stored procedure in response to the login event. This event is raised when a user session is established with an instance of SQL Server. The logon trigger fires after the logon authentication phase is complete and before the user session is actually established. Therefore, all messages (such as error messages and messages from the PRINT statement) that are inside the trigger and typically reach the user are routed to the SQL Server error log. If authentication fails, the logon trigger is not fired.

Four: how triggers work

When trigger triggers:

1. The system automatically creates the deleted table or the inserted table in memory;
2. Read-only, no modification is allowed, and the trigger is automatically deleted after execution is complete.

Inserted table:

1. An inserted or updated record line is temporarily saved;
2. You can check from the inserted table whether the inserted data satisfies the business requirements;
3. If not, a report error message is sent to the user and the insert operation is rolled back.

Deleted table:

1. The record line before deletion or update is temporarily saved;
2. The deleted data can be checked from the deleted table to meet the business requirements;
3. If not, the error message is reported to the user and the insert operation is rolled back.

Inserted table and deleted table contrast:

modifying Action Records	inserted Table	deleted table
Add (insert) record	storage of new records	............
Delete (deleted) Records	..............	to store deleted records
Modify (update) record	storage of updated records	Store pre-update records

V: Create a trigger

syntax for creating triggers:

CREATE TRIGGER trigger_name on table_name [with encryption] for  | After | INSTEAD of [DELETE, INSERT, UPDATE] as   T-SQL statement Go--with encryption represents the SQL text defined by the cryptographic trigger--delete,insert,update the type of the specified trigger

To prepare the test data:

--Create a student table created table student (    stu_id int identity (primary) key,    stu_name varchar (2),    Stu_gender Char ),    stu_age int)

1. Create an INSERT trigger

--Creating an INSERT trigger CREATE TRIGGER Trig_inserton studentafter insertasbegin    if object_id (n ' student_sum ', n ' U ') is null--determine if the Student_sum table exists create table        student_sum (stucount int default (0));--Create student_sum table to store student numbers    declare @stuNumber int;    Select @stuNumber = count (*) from student;    If not EXISTS (SELECT * from Student_sum)--Determine if there is a record in the table        insert into student_sum values (0);    Update student_sum set stucount [email protected]; --Insert the total number of students after the update into the Student_sum table end

---The test trigger trig_insert--> function is to insert data into the student cascade into the Student_sum table, update stucount--because it is a post-trigger, so the trigger Trig_insert is triggered after inserting the data first; INSERT into student (Stu_name,stu_gender,stu_age) VALUES (' bu ', ' men ', '), select Stucount Total number of students from Student_sum;    INSERT into student (Stu_name,stu_gender,stu_age) VALUES (' Marten cicada ', ' female ', ' + ');            Select Stucount total number of students from Student_sum;insert to student (stu_name,stu_gender,stu_age) VALUES (' Cao A deceive ', ' Male ', ' Max ');                Select Stucount total number of students from Student_sum;

After executing the above statement, the result is as follows:

Now that you have defined the total number of Students table Student_sum table is to insert data into the student table before the total number of students, so the student total table should prohibit users to insert data into it

--Create Insert_forbidden to prevent users from inserting data into the Student_sum table create trigger Insert_forbiddenon student_sumafter Insertasbegin    

--Trigger Trigger Insert_forbiddeninsert student_sum (Stucount) values (5);

The results are as follows:

2. Create a DELETE trigger

When the user performs a delete operation, the Delete trigger is activated, which controls the user's ability to delete data records from the database, and when the delete trigger is triggered, the deleted record is added to the deleted table, and the corresponding record of the original table is deleted. Therefore, view the deleted records in the deleted table.

--Creating a Delete trigger Create trigger Trig_deleteon student after Deleteasbegin    select stu_id as deleted student number, Stu_name Stu_gender , stu_age from    deletedend;

--Execute one by one DELETE statement trigger Trig_delete trigger delete from student where stu_id=1;

The results are as follows:

3. Create an UPDATE trigger

The update trigger is called when the user executes an UPDATE statement on the specified table, and this type of trigger is used to constrain the user's modifications to the data. Update triggers can do two things: the records before the update are stored in the deleted table, and the updated records are stored in the inserted table.

--Creating an UPDATE trigger CREATE TRIGGER Trig_updateon studentafter updateasbegin    declare @stuCount int;    Select @stuCount =count (*) from student;    Update student_sum set stucount [email protected];    Select stu_id as pre-update student number, stu_name as update student name from deleted    Select stu_id as after update student number, stu_name as after update student name from Inserteden D

--Create complete, execute an UPDATE statement trigger Trig_update trigger update student set Stu_name= ' Zhang Fei ' where stu_id=2;

4. Create an alternate trigger

Unlike the three after triggers described previously, SQL Server servers perform after-trigger SQL code by establishing a temporary inserted table and a deleted table, and then executing the code for the database operation before activating the code in the trigger. In the case of alternative (insteadof) triggers,when SQL Server executes the code that triggers instead OF triggers, a temporary inserted table and deleted table are created and then triggered directly instead of triggers, and a DML action statement that rejects user input is denied.

---Create instead OF trigger create trigger Trig_insteadofon student instead of Insertas begin    declare @stuAge int;    Select @stuAge = (select Stu_age from inserted) if (@stuAge >120)    select ' Insert age error ' as ' failure reason ' end

Create complete, execute an INSERT statement trigger trigger TRIG_INSTEADOF

5. Introduction to nested triggers

If a trigger invokes another trigger while it is executing, and the trigger then invokes the next trigger, a nested trigger is formed. Nested triggers are enabled at installation time, but you can use system stored procedures sp_configure to disable and re-enable nested triggers.

Nested triggers do not necessarily form a ring, it can t1->t2->t3 ... This continues to trigger, allowing nesting of up to 32 layers. If the number of nested occurrences exceeds the limit, then the trigger is terminated and the entire transaction is rolled back, and the following points need to be noted with nested triggers:

• By default, nested trigger configuration options are turned on.
• In the same trigger transaction, a nested trigger cannot be triggered two times.
• Because a trigger is a transaction, if an error occurs at any level of a series of nested triggers, the whole thing is canceled and all data is rolled back.

Nesting is an important feature to maintain the integrity of the entire database, but sometimes it may be necessary to disable nesting, and if nested is disabled, modifying the implementation of a trigger no longer triggers any triggers on that table. You need to disable nested triggers in the following situations:

• Nested triggers require complex and theoretical designs, and cascading modifications may modify data that users do not want to involve.
• Time modifications at any point in a series of nested triggers trigger triggers, although the database provides strong protection, but if you update the table in a specific order, it can cause problems.

use the following statement to disable nesting and enable nesting again:

--Disable nested EXCE sp_configure ' nested triggers ', 0;--enable nested exce sp_configure ' nested triggers ', 1;

6. Recursive triggers

a trigger's recursion is a trigger that activates the trigger from within, such as an UPDATE statement inside a data table that is activated by the update operation, which is likely to activate the trigger itself and, of course, there will be a judgment statement inside the recursive trigger. The T_SQL statement is executed only under certain circumstances, otherwise it becomes the dead loop of the wireless call.

recursive triggers in SQL Server include two types: direct recursion and indirect recursion.

• Direct recursion: The trigger is triggered and an action is taken, and the action is triggered again using a trigger.
• indirect recursion: The trigger is triggered and an action is taken, which in turn causes one of the triggers in the other table to be triggered, and the second triggers the original table to be updated, triggering the first trigger again.

by default, the recursive trigger option is disabled. Recursive triggers can only be recursive at most 16 levels, and if the 16th trigger in the recursion activates the 17th trigger, the result is the same as the published Rollback command, and all data is rolled back.

we explain the following examples, if there are table 1, table 2 names are T1, T2, on T1, T2, respectively, there are trigger G1, G2.

• indirect recursion: to T1 operation thereby triggering g1,g1 to T2 operation thereby triggering g2,g2 to T1 operation thereby triggering G1 again ...
• Direct recursion: to T1 operation thereby triggering g1,g1 to T1 operation thereby triggering G1 again ...

Set direct recursion:

direct recursion is disabled by default, and there are two methods to set to allow:

• t-sql:exec sp_dboption ' dbName ', ' recursive triggers ', true;
• EM: Options on the database, right-click Properties.

VI: Manage triggers

1. View triggers

(1). View all triggers in the database

--View all triggers in the database use database name goselect * from sysobjects where xtype= ' TR '

sysobjects holds the object of the database, where the record xtype as TR is the trigger object. We can see the name of the trigger in the Name column.

(2). Sp_helptext View Trigger Contents

Use database name goexec sp_helptext ' trigger name '

The trigger content is displayed in the style of the table.

In addition to triggers, sp_helptext can display the rules, default values, unencrypted stored procedures, user-defined functions, and the text of the view.

(3). Sp_helptrigger to view the properties of a trigger

Sp_helptrigger has two parameters: the first parameter is the table name, the second is the trigger type, the char (6) type, can be INSERT, UPDATE, DELETE, and if omitted, displays the properties of all types of triggers in the specified table.

Use database name Goexec sp_helptrigger tableName

2. Disable the Enable Trigger

Disabled: ALTER TABLE name disable TRIGGER trigger name
Enable: ALTER TABLE name enable TRIGGER trigger name

If there are multiple triggers, separate the name of each trigger with a comma.

If you change the trigger name to "All", you disable or enable all triggers for that table.

3 modifying triggers

--Modify TRIGGER syntax alter TRIGGER  trigger_name on  table_name [with      encryption] for      {[delete][,][insert][ ,][update]}     as       sql_statement;

4. Delete a trigger

--Syntax format:      DROP  TRIGGER   {TRIGGER} [,... n] Parameter: TRIGGER: Trigger name to delete N: Represents a placeholder that can delete multiple triggers       

SQL Server Basics (triggers)