禁止在没有触发器的情况下插入列

Question

我在表中有两个字段，Column1和Column2

Column1 | Column2
-------------------
 F      |   B
 A      |   C
--------------
 B           F    needs prevention
 C           A    needs prevention

如何在没有触发器的情况下阻止(B，F)和(C，A)插入？

Answer 1

因此，基本上您希望阻止与已有行完全相反的insert。

一种方法是添加check约束和唯一索引：

CREATE TABLE dbo.MyTable
(
    Column1 char(1) NOT NULL,
    Column2 char(1) NOT NULL,
    CONSTRAINT chk_Col1AndCol2 CHECK(Column1 <= Column2)
);
GO

CREATE UNIQUE INDEX UX_MyTable 
    ON dbo.MyTable (Column1, Column2);
GO

此检查约束可防止Column1持有的值大于Column2中的值。

如果您不想将column1限制为小于或等于column2，另一个选项是使用带有用户定义函数的check约束：

CREATE TABLE dbo.MyTable
(
    Column1 char(1) NOT NULL,
    Column2 char(1) NOT NULL
);
GO

CREATE UNIQUE INDEX UX_MyTable  -- Again, same unique index
    ON dbo.MyTable (Column1, Column2);
GO

CREATE FUNCTION fn_CheckMyTable
(
    @Column1 char(1),
    @Column2 char(1)
)
RETURNS int
AS
BEGIN

    RETURN 
    (
        SELECT COUNT(*)
        FROM MyTable
        WHERE Column1 = @Column2
        AND Column2 = @Column1
    )

END;
GO

ALTER TABLE MyTable
    ADD CONSTRAINT chk_MyTable1 CHECK(dbo.fn_CheckMyTable(Column1, Column2) = 0);
GO

Answer 2

您可以尝试使用check constraint来阻止这种情况

CREATE TABLE MY_TABLE (
    COLUMN1 varchar(255) NOT NULL,
    COLUMN2 varchar(255) NOT NULL,
    CONSTRAINT MY_CONSTRAINT CHECK ((COLUMN1, COLUMN2) NOT IN (('B','F'), ('C','A')))
);

当然，不同的提供程序的语法可能不同。

Answer 3

您可以使用TRIGGER_NESTLEVEL

CREATE TABLE t(col1 CHAR(5), col2 CHAR(5));
CREATE TABLE t_helper(i CHAR(5));

CREATE TRIGGER trg_t_helper ON t_helper
AFTER INSERT
AS
BEGIN
   INSERT INTO t(col1, col2)
   SELECT i, 'B'
   FROM inserted;
END;


CREATE TRIGGER trg_t ON t
AFTER INSERT
AS
BEGIN
  IF ((SELECT TRIGGER_NESTLEVEL( OBJECT_ID('trg_t_helper'),'AFTER', 'DML'))= 0)  
  RAISERROR('Direct data insert are disabled.',16,-1);
END;

检查：

INSERT INTO t_helper(i) VALUES ('Z');

SELECT * FROM t;
--col1  col2
--Z     B  

INSERT INTO t(col1, col2) VALUES ('A', 'B');

消息50000级别16状态1线路6

直接数据插入被禁用。