Create trigger insert_jy
On borrowing and lending
After insertion
be like
If (select count(*) from book，reader，inserted where book。 Total = inserted. Total number and readers. Library card number = inserted. Library card number) =0.
begin
Print "Incorrect Information!"
Rollback transaction
end
/* When modifying the reader's library card number in the reader table, modify the library card number in the borrowing table at the same time. */
Create trigger update_dz
On readers
as of last update
be like
If updated (library card number)
begin
Update library card number = (select library card number from insert) where library card number = (select library card number from delete)
end
/* When deleting the reader's information, check whether there is the reader's borrowing information in the borrowing table. If there is, it is not allowed to be deleted. */
Create trigger delete_dz
On readers
After deletion
be like
If (select count(*) from borrowing, inserted where borrowing. Library card number = inserted. Library card number) =0.
begin
Print' This information cannot be deleted!'
Rollback transaction
end
/* Create a stored procedure to reflect the book borrowing situation of designated readers. */
Create process select_id(@id int)
be like
Select * Borrowing Card Number from Borrowing Office =@id.
/* Create a stored procedure to add a reader record. Library number, unit, name, gender, professional title and address */
Create procedure insert_id(@ library card number int, @ company varchar(50), @ name varchar(6), @ gender varchar(4), @ title varchar( 10), @ address varchar(50)).
be like
Insert reader value (@ library card number, @ company, @ name, @ gender, @ title, @ address).
/* Create a stored procedure to modify book information. Total quantity, classification number, title, author, publishing unit, unit price */
Create procedure update_id(@ total number int, @ classification number int, @ title varchar(50), @ author varchar( 10), @ publisher varchar(20), @ unit price int).
be like
Update book collection classification number = @ classification number, title = @ title, author = @ author, publishing unit = @ publishing unit, unit price = @ unit price, in which the total number =@id.
/* Create a stored procedure to delete the information of the specified reader. */
Create process delete_id(@id int)
be like
Delete readers with library ID =@id