mysql触发器-限制存储在列中的值的数量 [英] mysql triggers - limiting the amount of values stored in a column

问题描述

数据库名称:库

Clients table: 
ID_number | Surname | Forename ...

Loans table: 
Loan_ID | Borrower | Book | Date

客户ID_Number是Loans.Borrower

Clients ID_Number is a foreign key in Loans.Borrower

我想将每个客户的贷款额限制为< = 5

I want to limit the amount of loans per client to <= 5

因此，如果在借款人"列中，ID_Number重复> 5次，则不会插入.

So if in Borrower column an ID_Number repeats > 5 times do not insert.

我将如何使用TRIGGER实施此操作?

How would I implement this using a TRIGGER?

到目前为止，我有:

CREATE TRIGGER limit_borrows BEFORE INSERT

ON Loans

FOR EACH ROW

BEGIN

[NO IDEA WHAT GOES HERE]

IF value in borrow column repeated > 5 
Do not insert

[NO IDEA WHAT GOES HERE]



END IF;

END // 

推荐答案

您需要计算if语句中id的数量并触发错误，以便不插入该行:

You need to count the number of id's in your if statement and trigger an error so that the row is not inserted:

CREATE TRIGGER limit_borrows BEFORE INSERT

ON Loans

FOR EACH ROW

BEGIN
DECLARE _id (borrower id data type here)

SELECT borrower from loans where loan_ID = new.loan_ID into _id;

SET @count = (select count(1) from loans where borrower_id = _id ;
IF @count > 5
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'your error message';

END IF;

END // 

这篇关于mysql触发器-限制存储在列中的值的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！