在 SQL 中设置表行数限制

发布于 2024-12-12 14:04:27 字数 47 浏览 0 评论 0原文

我想设置表的行数限制。我该怎么做呢？

例如我的表中有 50 行。

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

薆情海 2024-12-19 14:04:28

使用 CHECK 约束。例如：

CREATE TABLE t1 (x TINYINT NOT NULL UNIQUE CHECK (x BETWEEN 1 AND 50));

Use a CHECK constraint. E.g.:

CREATE TABLE t1 (x TINYINT NOT NULL UNIQUE CHECK (x BETWEEN 1 AND 50));

回复收藏 0 原文

说谎友 2024-12-19 14:04:28

您指的是限制查询结果吗？

如果是这样，在 SQL Server 2008 中，您可以使用 TOP

SELECT TOP 50 *
FROM Table

如果您正在考虑实际限制数据库表中的记录数量，则可以使用 TRIGGER 中的 IF 语句，就像@Shark 已发布，将是我的解决方案。

Are you referring to limiting the results of a query?

If so, with SQL Server 2008 you can use 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 @Shark has posted, would be my solution.

回复收藏 0 原文

最佳男配角 2024-12-19 14:04:28

您想要的是有一个 INSTEAD OF INSERT 触发器来检查当前行数。如果已经 50，您将使用 RAISERROR 引发错误。如果没有，您只需插入该记录。

警告！前面有未经测试的代码。它可能包含拼写错误或轻微的语法错误。该代码应该向您展示所涉及的概念。根据您的需求进行相应调整。

像这样：

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

What you want is having a INSTEAD OF INSERT trigger that checks the # of current rows. If already 50, you will raise an error by using RAISERROR. If not, you just insert the record.

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

回复收藏 0 原文

九歌凝 2024-12-19 14:04:27

在表上创建一个 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

Create an AFTER INSERT trigger on the table. Here's something that would be relatively effective with your requirement:

create trigger LimitTable
on YourTableToLimit
after insert
as
    declare @tableCount int
    select @tableCount = Count(*)
    from YourTableToLimit

    if @tableCount > 50
    begin
        rollback
    end
go

回复收藏 0 原文

~没有更多了~