SQL Server triggers enable simultaneous addition, deletion, and update of multiple tables

SQL Server triggers enable simultaneous addition, deletion, and update of multiple tables

Definition: What is a trigger? In SQL Server is a certain operation of a table, triggering certain conditions, thus executing a program. A trigger is a special stored procedure.
There are three common triggers: Apply to insert, Update, and Delete events, respectively. (SQL Server 2000 defines a new trigger, which is not mentioned here)

Why should I use triggers? For example, so two tables:

Create table Student (--Student table
StudentID int PRIMARY KEY,--school number
....
)

Create table Borrowrecord (--Student library record
Borrowrecord int Identity (1,1),--Serial number
StudentID int,--School number
Borrowdate datetime,--loan time
Returndate Datetime,--return time
...
)

Create add, delete, and update triggers (Trigger) separately to achieve data synchronization between two tables.

1: Data synchronization increases:

If you have two tables--a tables and B tables, create triggers so that B tables insert data synchronously after table A is inserted into the data. Where the B table inserts the data, the fields need to correspond to the fields in table A.

CREATE TRIGGER Trigger Name
On a table
After INSERT
As BEGIN INSERT into
b Tables (b Table fields 1,b table fields 2,b table fields 3)
SELECT a table field 1,a table field 2,a table Field 3
From INSERTED
End

2. Data Synchronization deletion:

If you have two tables--a tables and B tables, create triggers to delete data in Table B after table a deletes the data. where B table and a table should have corresponding primary key association.

CREATE TRIGGER Trigger Name
On a table
After DELETE
As BEGIN DELETE B table
WHERE
Table B primary Key in (
SELECT a table primary key
From DELETED)
End

3. Data Synchronization Update:

If you have two tables--a tables and B tables, create triggers to synchronize the data in table B after update a table data.

CREATE TRIGGER Trigger Name
On a table
After UPDATE
As
Update B Table
SET
b.b table field 1=a.a table Field 1
From
B table as b,inserted as A
WHERE b.b Table primary key =A.A table primary key

Why should I use triggers? For example, so two tables:

     Create Table Student (             --Student table
       StudentID int primary key,      --School Number
       ....
     )

     Create Table Borrowrecord (              -Student Library record table
       borrowrecord  int identity (1,1),      --Serial number  
       studentid      INT,                   -School Number
       borrowdate    datetime,               --Lending time
        returndate    datetime,               -return time
       ...
    )

The functions used are:
1. If I change the student's number, I hope his library record is still relevant to the student (i.e., changing the school number of the library record at the same time);
2. If the student has graduated, I would like to delete his study number while also deleting its library record.
Wait a minute.

Triggers can be used at this time. For 1, create an UPDATE trigger:

    Create Trigger trustudent
      on student                         -Create triggers in the student table
      for update                         -- Why events Trigger
    as                                        -What to do after the event is triggered
      if Update (StudentID)           
      begin

Update Borrowrecord
Set Studentid=i.studentid
From Borrowrecord BR, Deleted D, Inserted i--deleted and Inserted temporary tables
Where Br. Studentid=d.studentid

End

Understand the two temporary tables inside the trigger: Deleted, Inserted. Note that deleted and inserted respectively represent the "old record" and "new record" of the table that triggered the event.

    a Database tutorial system two virtual tables are used to store information that changes are recorded in a table, respectively:
                              Virtual Table inserted                     Virtual table deleted

Store new records when table records are new do not store records
New records to be updated when modified
Delete records are not stored when the record is deleted

The process of an update can be seen as: Generate new records to inserted tables, copy old records to deleted tables, and then delete student records and write record entries.

For 2, create a DELETE trigger
Create Trigger Trdstudent
On Student
For Delete
As
Delete Borrowrecord
From Borrowrecord BR, delted D
Where Br. Studentid=d.studentid

From these two examples we can see the key to the trigger: A.2 a temporary table; B. Trigger mechanism.
Here we only explain the simplest triggers. Complex capacity after the description.