CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012constraints
dotnetN00b
dotnetN00b

Reputation: 5131

Constraint based on relationship of two tables?

Basically I want to only allow an edit/update in Table A when column in Table B is NULL. Table A and Table B reference the same primary key id. So something like;

-- Only allow Table A to be updated when Table B column is NULL

update TableA
set blah = @a, foo = @b
from tablea
inner join tableb on tablea.id = tableb.id
where tableb.column is null

Is there some way to use this as a constraint on Table A?

Upvotes: 0

Views: 47

Answers (1)

M.Ali
M.Ali

Reputation: 69574

CREATE TRIGGER tr_Instead_Update_Table_A
ON TABLEA
INSTEAD OF UPDATE
AS
BEGIN
  SET NOCOUNT ON;

   IF NOT EXISTS(SELECT 1 
                FROM deleted d INNER JOIN TableB B
                ON d.PK_Column = B.PK_Column
                WHERE B.ColumnName IS NULL)
    BEGIN
        RAISERROR('Invalid update',16,1)
        RETURN;
    END

  -- Your update statement here 

END

Upvotes: 1

Related Questions