CODEWITHSUNDEEP
sqlsql-server-2012
Matt Oddy
Matt Oddy

Reputation: 1

Prevent column change without the other

No-one should be allowed to update the customer address column unless the postcode column is also updated. If an attempt is made to update one without the other, then a trigger will fire and the user will be shown an error message.

Any help on how I can do this in Microsoft SQL Server 2012 will be appreciated!

Upvotes: 0

Views: 103

Answers (1)

FLICKER
FLICKER

Reputation: 6683

You can use below logic

CREATE TRIGGER [dbo].AU_MyTrigger ON [dbo].MyTable
FOR  UPDATE
AS 
BEGIN
    declare @bu_addr varchar(100)
    declare @bu_zip varchar(100)
    declare @au_addr varchar(100)
    declare @au_zip varchar(100)

    select @bu_addr = addr, @bu_zip = zip from DELETED
    select @au_addr = addr, @ay_zip = zip from INSERTED
    if (@bu_addr <> @au_addr) and (@bu_zip = @au_zip)
    BEGIN
        -- update table with old values
        -- raise error
    END
END

Note that if this update can happen in batch, you need to loop through each record and update their value to old and only return error at the end of trigger (outside of loop). In that case, for iterating on updated rows, you need to use CURSOR

You case might not be as easy as I explained, but this is the approach that works.

Upvotes: 1

Related Questions