允许用户在没有触发器的情况下输入受限记录 [英] Allow users to enter limited records without Triggers

问题描述

我有一个像这样的表
EMP(name varchar(45)，sal money ...);

我希望用户仅输入最多20条记录..如果输入了第21条记录，则必须引发异常...我想在不使用触发器的情况下执行此操作.

请帮助我

I have a table structured like this
EMP(name varchar(45), sal money...);

i want the users to enter up to 20 records only..if 21 st record is entered an exception has to be raised... i want to do this witout the use of triggers..

Please help me

推荐答案

我看到有两个地方可以做到这一点.首先，您可以创建一个存储过程，最多将二十条记录作为输入.如果(一次)传递了多个错误，则存储过程可能会引发错误.这里的缺点是您可以多次调用存储过程.但是，您可能会变得很复杂，并让存储过程在表中查找并确保用户在插入开始之前尚未输入20个值.

另一个选择是将该逻辑放入您的应用程序.让应用程序在表上进行查找，以查看用户已经输入了多少条记录.然后，仅允许添加剩余的记录数.

无论您做什么，它都不会像触发器那样简单，但是可以使用上面列出的两种方法之一来满足您的要求.

There are two places that I see that this could be done. First, you could create a stored procedure that would take up to twenty records as an input. The stored procedure could throw an error if more than that were passed in (at once). The downside here is that you could call the stored procedure more than once. However, you could get complicated and have the stored procedure look up the table and ensure that the user had not already entered 20 values before the insertion began.

The other option would be to put this logic into your application. Have the application do a lookup on the table to see how many records the user had already entered. Then, only allow the remaining number of records to be added.

No matter what you do, it won''t be as foolproof as a trigger, but it is possible to achieve your requirements using either of the two methods listed above.

您好，
使用这个:

Hi,
Use this:

DECLARE @count INT
SET @count = (SELECT COUNT(*) FROM test1)
IF @count > 20
BEGIN
   PRINT 'You cannot insert more than 20 records'
END
ELSE
   INSERT INTO test1 VALUES('1', 'Amit', 'Bangalre')

祝一切顺利.
--Amit

All the best.
--Amit

这篇关于允许用户在没有触发器的情况下输入受限记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！