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.

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,'test@gmail.com','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  | test@gmail.com | 10.100.100.100 |             |

--Attempt update for hotmail.com - should not update
update Tracked set email_address = 'test@hotmail.com'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test@gmail.com | 10.100.100.100 |             |

--Attempt update for gmail.com - should update
update Tracked set email_address = 'test1@gmail.com'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test1@gmail.com | 10.100.100.100 |             |

--Attempt update for gmail.co.uk - should not update
update Tracked set email_address = 'test2@gmail.co.uk'
select * from Tracked

| id | email_address   | ip_address     | trackedPage |
|----|-----------------|----------------|-------------|
| 1  | test1@gmail.com | 10.100.100.100 |             |

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.