Ernio Ernio - 11 months ago 42
SQL Question

Check if (during insertion) building has always same adress

I am writing TRIGGER INSTEAD OF INSERT which validates data before insertion.

To simplify my problem:
Consider this as my insertion:

('A', 'building1', 'adress1'),
('B', 'building2', 'adress2'),
('C', 'building1', 'adress3')

I need to catch case in which INSERTED table contains same building with different adresses - which is not possible.

Some IF statement snippet would really help me here.

Answer Source

Best solution is provided by Gordon. If normalizing the structure is not an option, you can consider an Unique Index on combination of 2 columns building, address. If this is also not an option and you really need to create a trigger:

if exists(select building 
          from inserted 
          group by building 
          having count(distinct address) > 1)
    --your logic here