在将行输入到表中时，如何检查来自另一个表的约束？

Question

假设我有两个表Account和tables。帐户具有属性Balance。每次在表中输入一个值，提取时，我想检查是否有足够的余额来这样做。如果是的话，我想从余额中减去这笔钱。

这并不是我想要做的，而是我需求的一个简化版本。

PS：“检查约束”是正确的说法吗？我不是很确定。谢谢!

Answer 1

这是一个简短的示例：

INSERT INTO mytable 
            (id, name) 
SELECT 1, 'test' 
WHERE  NOT EXISTS(SELECT id 
                  FROM   myanother_table 
                  WHERE  id = 1) 

注：如果不理解或看一看OP的代码，就很难假设任何事情。所以我刚刚提供了一个例子。

Answer 2

您可以创建一个函数来验证余额是否大于您的@值。

然后将该函数添加到约束中。

试着看看这个：

Can a Check constraint relate to another table?

Answer 3

你想要的不是约束。你要找的是导火索。看一下这个例子。

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Account]')
                    AND type IN ( N'U' ) )
    DROP TABLE [dbo].[Account]
GO

CREATE TABLE dbo.Account
    (
      AccountID INT NOT NULL ,
      AccountBalance DECIMAL(19, 2) NOT NULL
    )
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[Transaction]')
                    AND type IN ( N'U' ) )
    DROP TABLE [dbo].[Transaction]
GO

CREATE TABLE dbo.[Transaction]
    (
      TransactionID INT NOT NULL
                        IDENTITY(1, 1) ,
      AccountID INT NOT NULL ,
      TransactionAmount DECIMAL(19, 2) NOT NULL
    )
GO

CREATE TRIGGER dbo.TRI_Transaction ON dbo.[Transaction]
    AFTER INSERT
AS
    UPDATE  a
    SET     a.AccountBalance = a.AccountBalance + i.TransactionAmount
    FROM    Account a
            JOIN INSERTED i ON ( a.AccountID = i.AccountID )
GO

INSERT  INTO dbo.Account
        ( AccountID, AccountBalance )
VALUES  ( 1234, 0 )

SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )

INSERT  INTO dbo.[Transaction]
        ( AccountID, TransactionAmount )
VALUES  ( 1234, 10 )

SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )


INSERT  INTO dbo.[Transaction]
        ( AccountID, TransactionAmount )
VALUES  ( 1234, 20 )

SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )

INSERT  INTO dbo.[Transaction]
        ( AccountID, TransactionAmount )
VALUES  ( 1234, -15 )


SELECT  *
FROM    dbo.Account a
        LEFT OUTER JOIN dbo.[Transaction] t ON ( a.AccountID = t.AccountID )