CodePudding user response:
The building Lord, you this topic is too big, I recommend you to the library to borrow books, a lot of books, library borrowing system is some, can consult,CodePudding user response:
Do not ask where isCodePudding user response:
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 1, did not test, 2, and more business to add other constraints -- -- -- -- -- -- --
- create a table
If not OBJECT_ID (N 'tempdb for.. # books') is null
Drop table # books
Go
The create table # books (
Serial number int,
Number varchar,
The title varchar,
Inventory int,
Creation date datetime
)
Go
If not OBJECT_ID (N 'tempdb for.. # users') is null
Drop table # user
Go
The create table # users (
Serial number int,
Number varchar,
The name varchar,
Creation date datetime
)
Go
If not OBJECT_ID (N 'tempdb for.. # ') is null
Drop table # record
Go
The create table # record (
Serial number int,
User id varchar,
Book number varchar,
Number of varchar,
Borrow date varchar,
Expected return date varchar,
The actual due date varchar,
State varchar
)
Go
- insert the data
Insert into # books
Select '1', '20191107001', 'test 1, 155, getdate () union all
Select '2', '20191107002', '2' test, 55, getdate () union all
Select '3', '20191107003', 'test 3', 62, getdate ()
Go
Insert into # user
Select '1', '191107001', 'zhang SAN, getdate () union all
Select '2', '191106011', 'li si, getdate () union all
Select '3', '191105053', 'Cathy', getdate ()
Go
Insert into # record
Select '1', '191107001', '20191107002', '2', GETDATE (), GETDATE () + 30, "', 'still not' union all
Select '1', '191105053', '20191107002', '50', GETDATE (), GETDATE () + 30, "', 'still not' union all
Select '1', '191105053', '20191107003', '20', GETDATE (), GETDATE () + 30, "', 'still not' union all
Select '1', '191106011', '20191107001', '11', GETDATE (), GETDATE () + 30, "', 'still not'
Go
- the trigger (according to the business by adding other triggers)
If (object_id (' tgr_classes_update ', 'the TR) is not null)
Drop the trigger trg_ books
Go
Create the trigger trg_ books
On # books
For the delete
As
Declare @ oldName varchar (20)
Select @ oldName=number from inserted;
If (the exists (select * from number=@ # record where books oldName and status & lt;> 'is also' and quantity & gt; 0))
The begin
Print 'cannot be deleted, there have not returned the books! ';
End
The else
The delete from where id=@ # books oldName
Print 'delete success;
Go
CodePudding user response:
The