First, a few nonsense words are as follows:
(1) trigger is a special stored procedure. Its execution does not need to be explicitly called, but is triggered by some events, this is a bit similar to the Event Processing Mechanism in C. A trigger is triggered when one or more of the UPDATE, INSERT, or DELETE operations are performed on the relevant tables of the specified database.
(2) triggers can contain complex SQL statements, which are mainly used to force complex business rules or requirements.
(3) The trigger can maintain the integrity of the database. When the insert, update, or delete operation is performed, the trigger forcibly maintains the data integrity based on the relationship between the table and the table.
Well, after you have finished pasting the code, first paste the columns in the two tables I created. Their associations are one-to-many and are associated with UserID.
Then a very simple trigger
Copy codeThe Code is as follows:
If exists (SELECT * FROM sysobjects
WHERE name = 'tr _ users_onupdate' and type = 'tr ')
Drop trigger tr_Users_OnUpdate
GO -- create a trigger here is similar to the stored procedure (all DDL statements)
-- Delete and recreate a trigger with the same name first.
Create trigger tr_Users_OnUpdate
ON Users FOR UPDATE
As print 'users table modified'
GO
In the above Code, tr_Users_OnUpdate is the trigger name and Users is the table name. This trigger is used to print "the Users table has been modified" when an Update is executed to the Users table ".
Now we can see that the trigger is not very practical. Next we will learn about the two special tables "inserted" and "deleted" in the trigger ". These two tables are mainly used for triggers. The Deleted table is used to store copies of the rows affected by the DELETE and UPDATE operations. The Inserted Table is used to store copies of rows affected by the INSERT and UPDATE statements. Then, we can see that records are stored in "inserted" and "deleted" when performing the UPDATE operation ". In fact, it is difficult to understand that the deleted table stores records before Update, while the inserted Table stores records after Update. I will not repeat the theoretical content here, more details are provided on official materials.
What we need to do now is the focus of this Article. When adding a record to WordInfo, use the trigger to increase the LeaveCount field of the corresponding record of UserInfo by 1. The Code is as follows:
Copy codeThe Code is as follows:
-- Add a message trigger
If exists (SELECT name FROM sysobjects WHERE name = 'tr _ leaveword_add' and type = 'tr ')
Drop trigger tr_LeaveWord_Add
GO
Create trigger tr_LeaveWord_Add
ON WordInfo FOR INSERT
As update UserInfo SET LeaveCount = LeaveCount + 1
WHERE UserID = (select top 1 UserID FROM Inserted)
GO
OK. You can close the job here. It is worth noting that if the trigger is triggered by UPDATE, You can query the data before updating and then query the deleted table.
