Checking update value before update

Question

First time posting so apologies if this has been asked already.

I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come to the conclusion I need a trigger that will fire before the update happens to check to see if the email_address is valid.

I have two tables tracked and trackOmit.

Tracked
  id
  email_address
  ip_address
  trackedPage

trackOmit
  id
  trackOmit_email

TrackOmit is a list of email domains "hotmail.com, gmail.co.uk" etc, that for whatever reason we don't want tracking, however due to bad design the update statement doesn't take into account the trackOmit table. I do not know the first place to start to write a trigger to check the updated values against the trackOmit table before update.

Thanks for any help that can be provided.

Answer 1

Here is an example of an INSTEAD OF TRIGGER.

--demo setup
USE [Test]
GO

drop table if exists Tracked;
drop table if exists TrackOmit;
Create table Tracked (id int, email_address varchar(100), ip_address varchar(50), trackedPage varchar(100))
create table TrackOmit (id int, trackOmit_email varchar(100))

insert into Tracked(id, email_address, ip_address, trackedPage) values
(1,'[email protected]','10.100.100.100', ' ')

insert into TrackOmit(id,trackOmit_email) values(1,'hotmail.com'),(2,'gmail.co.uk')
-----------------
--create instead of trigger
DROP TRIGGER IF EXISTS [dbo].[TrackedInsteadOfUpdate];
GO
CREATE TRIGGER [dbo].[TrackedInsteadOfUpdate] ON [dbo].[Tracked]
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON

    --only update rows where the updated email_address
    --is not on the TrackOmit table
    UPDATE t
    SET t.email_address = i.email_address
    FROM dbo.tracked t
    JOIN inserted i
        ON i.id = t.id
            AND NOT EXISTS (
                SELECT *
                FROM TrackOmit o
                WHERE i.Email_Address LIKE '%' + o.trackOmit_email + '%'
                )
END
GO

ALTER TABLE [dbo].[Tracked] ENABLE TRIGGER [TrackedInsteadOfUpdate]
GO

---

--original values in Tracked
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | [email protected] | 10.100.100.100 |             |

---

--Attempt update for hotmail.com - should not update
update Tracked set email_address = '[email protected]'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | [email protected] | 10.100.100.100 |             |

---

--Attempt update for gmail.com - should update
update Tracked set email_address = '[email protected]'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | [email protected] | 10.100.100.100 |             |

---

--Attempt update for gmail.co.uk - should not update
update Tracked set email_address = '[email protected]'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | [email protected] | 10.100.100.100 |             |