Prevent from inserting columns without triggers

I have a two field in table, Column1 and Column2

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

how can I prevent (B,F) and (C,A) from inserting without the triggers?

3 answers

  • answered 2018-01-14 08:30 Andrei Amarfii

    You may try to prevent this using 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')))
    );
    

    Of course the syntax may differ from provider to provider.

  • answered 2018-01-14 08:30 lad2025

    You could use 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;
    

    Check:

    INSERT INTO t_helper(i) VALUES ('Z');
    
    SELECT * FROM t;
    --col1  col2
    --Z     B  
    
    INSERT INTO t(col1, col2) VALUES ('A', 'B');
    

    Msg 50000 Level 16 State 1 Line 6

    Direct data insert are disabled.

    DBFiddle Demo

  • answered 2018-01-14 08:30 Zohar Peled

    So basically you want to prevent an insert that will be the exact opposite from a row you already have.

    One way to do it is by adding a check constraint and a unique index:

    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
    

    This check constraint prevents Column1 to hold values that are bigger than the values in Column2.

    If you don't want to limit column1 to be smaller or equal to column2, another option is to use a check constraint with a user defined funtion:

    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