) Small trigger example

The trigger can carry out some constraints.

This is a small example. When the StudentID column of the Student table is changed, the StudentID column of the BorrowStudent table is also changed. If the Student table deletes a record, BorrowStudent also deletes the corresponding StudentID record.


/* Delete the table to be created first */

Drop table if exists Student;

Drop table if exists BorrowStudent;


/* Create a table */

Create table Student (

StudentID int not null primary key,

StudentName varchar (30) not null,

StudentSex enum ('M', 'F') default 'M'

) Engine = myisam;


Create table BorrowStudent (

BorrowRecord int not null auto_increment primary key,

StudentID int not null,

BorrorDate date,

ReturnDate date,

Foreign key (StudentID) references Student (StudentID)

) Engine = myisam;


/* Insert record */

Insert into Student values (1235412, 'java', 'M ');

Insert into Student values (3214562, 'jiajia ', 'M ');

Insert into Student values (5441253, 'purana ', 'F ');


Insert into BorrowStudent (StudentID, BorrorDate, ReturnDate)

Values (1235412, '2017-01-01 ', '2017-01-07 ');

Insert into BorrowStudent (StudentID, BorrorDate, ReturnDate)

Values (3214562, '2017-01-01 ', '2017-01-07 ');

Insert into BorrowStudent (StudentID, BorrorDate, ReturnDate)

Values (5441253, '2017-01-01 ', '2017-01-07 ');


/* Create a trigger */

Delimiter $

Drop trigger if exists tduStudent $

Drop trigger if exists tddStudent $

Create trigger tduStudent before update

On Student for each row

Begin

If new. StudentID! = Old. StudentID then

Update BorrowStudent

Set BorrowStudent. StudentID = new. StudentID

Where BorrowStudent. StudentID = old. StudentID;

End if;

End $


Create trigger tddStudent before delete

On Student for each row

Begin

Delete

From BorrowStudent

Where BorrowStudent. StudentID = old. StudentID;

End $

Delimiter;