在 SQL 中为表行设置限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7930286/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Set Limit for a Table Rows In SQL
提问by ahmadali shafiee
I want to set the limit for my table's rows. How can I do it?
我想为我的表的行设置限制。我该怎么做？
For example 50 rows in my table.
例如我的表中有 50 行。
回答by
Create an AFTER INSERTtrigger on the table. Here's something that would be relatively effective with your requirement:
AFTER INSERT在表上创建触发器。以下是对您的要求相对有效的内容：
create trigger LimitTable
on YourTableToLimit
after insert
as
declare @tableCount int
select @tableCount = Count(*)
from YourTableToLimit
if @tableCount > 50
begin
rollback
end
go
回答by nvogel
Use a CHECK constraint. E.g.:
使用 CHECK 约束。例如：
CREATE TABLE t1 (x TINYINT NOT NULL UNIQUE CHECK (x BETWEEN 1 AND 50));
回答by Curt
Are you referring to limiting the results of a query?
您是指限制查询结果吗？
If so, with SQL Server 2008 you can use TOP
如果是这样，您可以使用 SQL Server 2008 TOP
SELECT TOP 50 *
FROM Table
If you're looking at actually limiting the amount of records in the database table, then an IF statement in a TRIGGER, like @Sharkhas posted, would be my solution.
如果您正在考虑实际限制数据库表中的记录数量，那么 a 中的 IF 语句TRIGGER（如@Shark已发布）将是我的解决方案。
回答by Adriano Carneiro
What you want is having a INSTEAD OF INSERTtrigger that checks the # of current rows. If already 50, you will raise an error by using RAISERROR. If not, you just insertthe record.
你想要的是有一个INSTEAD OF INSERT触发器来检查当前行的数量。如果已经是 50，您将通过使用RAISERROR. 如果没有，你只是insert记录。
Warning!Untested code ahead. It might contain typos or slight syntax errors. The code is supposed to show you the concepts involved. Tweak and adjust to your needs accordingly.
警告！未经测试的代码。它可能包含拼写错误或轻微的语法错误。该代码应该向您展示所涉及的概念。相应地调整和调整您的需求。
Like this:
像这样：
CREATE TRIGGER checktablelimit
ON yourtable
INSTEAD OF INSERT
AS
DECLARE @currentCount INT
SELECT @currentCount = COUNT(*)
FROM yourtabletolimit
IF @currentCount = 50
BEGIN
RAISERROR ('Table already has 50 records',
11,
1);
END
ELSE
BEGIN
INSERT INTO yourtable
(field1,
field2,
field3)
SELECT field1,
field2,
field3
FROM inserted
END
GO
