[Database] An example of using trigger to audit database tables (Audit)-for MS SQL

First, create the database test and create two tables. we name it grades and audit, and use audit to audit grades. Grades is used to store students' scores, including "sdudentid, courseid, grade. The primary key is sdudentid and courseid. Audit only adds two fields: changetype and changtime to record the type and modification time of the grades table.
The SQL statement for creating two tables is as follows:
Create Table grades (
Studentid int,
Courseid int,
Grade int,
Primary Key (studentid, courseid)
);
Create Table audit (
Changetype char (15 ),
Changetime datetime,
Studentid int,
Courseid int,
Grade int,
Primary Key (changetype, changetime, studentid, courseid, grade)
)

Create a trigger on the grades table. If you perform the insert, delete, and update operations on the grades table, the types of these operations are recorded in the audit table (Update (old, new), insert or delete), operation time, data affected by the operation (such as deleted data, inserted data, pre-modified data, and modified data ).
The SQL language used to create a trigger is as follows:
/* The Beginning of the trigger definition */
Create trigger tr_gradeschanged on grades for delete, insert, update
As
Declare @ insertedcount int
Declare @ deletedcount int
Declare @ changetype char (10)
Declare @ changetime datetime
Declare @ updatetype char (4)
/* There are two temporary tables in ms SQL Server that store deleted and inserted records, respectively "deleted" and "inserted ". Update can be regarded as one deletion and one addition */
Select @ insertedcount = count (*) from inserted
Select @ deletedcount = count (*) from deleted
Select @ changetype =
Case
When @ insertedcount> 0 and @ deletedcount> 0
Then 'update'
When @ insertedcount = 0 and @ deletedcount> 0
Then 'delete'
Else 'insert'
End
Select @ changetime = getdate ()
Select @ updatetype =''
If @ changetype = 'update' select @ updatetype = 'old'

Insert into audit (changetype, changetime, studentid, courseid, grade) Select @ changetype + @ updatetype, @ changetime, studentid, courseid, grade from deleted

If @ changetype = 'update' select @ updatetype = 'new'

Insert into audit (changetype, changetime, studentid, courseid, grade) Select @ changetype + @ updatetype, @ changetime, studentid, courseid, grade from inserted

/* The End Of The trigger definition */

Now, the entire trigger has been created. We can test this trigger.

Run the following statement:
Delete from grades
Delete from audit
Insert into grades values (19224,002, 90)
Update grades set grade = 100 Where studentid = 19224
Delete from grades where Fig = 19224
Select * from audit order by changetime ASC
The following result is displayed:

We can see that the audit table monitors operations on the grades table and records the changes. Because the interval between the preceding test statements is too short, changetime is the same. The query results are first sorted in ascending order of changtime and then in ascending order of changtype (default, so it is a little different from the actual results. In theory, it should be:
Changtype changetime studentid courseid grade
1 insert ----------- 19224 2 90
2 Update Old ----------- 19224 2 90
3 Update new ------------- 19224 2 100
4 Delete ---------- 19224 2 100
<! --------------------------------------- The end ------------------------------------------------------------------->