Current location - Trademark Inquiry Complete Network - Futures platform - SQL trigger programming
SQL trigger programming
1. Create the trigger ins _ Borrow _ tr in the borrowing table. When inserting records into the borrowing table, the borrowing date cannot be blank, and the return date must be blank. If the above conditions are not met, records are not allowed to be inserted in the Borrowing table; If the above conditions are met, the insertion is allowed, and the expiration date of the book is obtained at the same time, then the corresponding number of books in the Book table should be reduced by 1, which corresponds to the reader's borrowing behavior. -"Loan Date" cannot be empty; It can be set through IS NOT NULL without a trigger; -The book return date must be empty, which can be achieved by setting the default value, that is, whatever value is entered will be cleared in the end; —— "Borrowing Date" is calculated according to "Borrowing Date", and the number of books in the "Books" table should be reduced by 1: two functions are realized by a trigger; Create trigger ins _ debit _tr

lend

Used to insert

be like

begin

Update due date of borrowing set = borrowing date +n From the borrowing date, insert the borrowing place. B _ id, = inserted. B _ id and others are similar.

end

Start 2. Create the trigger del _ Borrow _ tr in the borrowing table. When deleting a record in the borrowing table, if the return date is blank, the record cannot be deleted. -This can be achieved by a trigger. 3. Create a trigger update_return_tr in the "Borrow" table. When updating records in the Borrowing table, only the Return Date field can be updated, and other fields cannot be updated. Please calculate whether it is overdue and give the penalty information (one day overdue is 0. 1 yuan). If updated, the number of books in the Books table should be increased by 1. This operation corresponds to the reader's book return behavior. -Only the "Return Date" field can be updated, but other fields cannot be updated: this is not easy to implement, because if the program is writable, it will be a bit troublesome! It is recommended that other fields be read-only when modified in the program. -Other parts are implemented by triggers.