Help writing SQL Trigger

Hello, I need help in writing a Trigger that will fire when the field AssignedTo if update in table TERRITORIES
This are the tables and fields I am working with:
TERRITORIES, AssignedTo, DateAssigned, Territory
USERS, LastFirst, eMail
DATA AssignedTo, Territory

This is the way the process needs to work:

1. When TERRITORIES.AssignedTo is updated by a user
2. IF Not Is Null TERRITORIES.AssignedTo Then
3. TERRITORIES.DateAssigned = GetDate()
4.Then find USERS.LastFirst = TERRITORIES.AssignedTo and read the column called eMail for this user
5. Then update DATA.AssignedTo with that user eMail where TERRITORIES.Territory = DATA.Territory
6. Else ‘ It the TERRITORIES.AssignedTo changes from a name to Null
7. TERRITORIES.DateAssigned = Null
8. DATA.AssignedTo = Null where TERRITORIES.Territory = DATA.Territory
9. End If

Let me know if you need clarification in any of the pseudo steps
Cobra967Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
create trigger trg_territories
on territories
for update
as
begin
  set nocount on

   --- part 1: AssignedTo is set, update the 2 tables
   update t
         set t.DateAssigned = GetDate() 
     from inserted i
     join territories t on t.Territory = i.Territory 
   where i.AssignedTo is not null

   update d
       set assignedTo  = u.email
    from inserted i
    join users u on u.LastFirst = i.AssignedTo
    join data d on d.Territory = i.Territory
   where i.AssignedTo is not null

   --- part 2: Assigned is cleared, update the 2 tables
   update t
         set t.DateAssigned = null
     from inserted i
     join territories t on t.Territory = i.Territory 
   where i.AssignedTo is null

  update d
       set assignedTo = null
     from inserted i
     join data d on d.Territory = i.Territory 
   where i.AssignedTo is null
 
end

Open in new window


this article should help to understand the UPDATE/JOIN syntax:
https://www.experts-exchange.com/articles/1517/UPDATES-with-JOIN-for-everybody.html

code note tested, there may be typos in it ...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cobra967Author Commented:
WOW! Thank you very much!!!!! That was very fast. However, the first part works just jut but the second part is being ignorer. So, if I remove AssignedTo from TERRITORIES the trigger does not clear the AssignedTo in Data and does not remove the DateAssigned on TERRITORIES :-(
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify "remove", how exactly does this happen? do you set NULL or an empty string?
if it's an empty string, this needs to be handled differently
0
ste5anSenior DeveloperCommented:
Just some comments:

Why do you store the e-mail address in a separate table? This is redundancy and can lead to an anomaly.

Consider this:

1. User A with e-mail address a@a.com is assigned to a territory A.
Contenent of DATA: Territory A, E-Mail a@a.com

2. User A changes his e-mail address, e.g. got married, to b@a.com.
Contenent of DATA: Territory A, E-Mail a@a.com

Now any mail maybe send to a dead mailbox.

Why not using a view instead?

And then: Assigning a NULL to AssignedTo is a data model error. You should have two tables. One defining the territories and on the assignments. When an assignment is canceled, then the row must be deleted from the assignments table. This would also solve the DateAssigned NULL problem.
0
Cobra967Author Commented:
You are correct Guy, after testing the trigger by updating the AssignedTo field using the application it works just fine, so the app does actually set the field to NULL.  Before, I was testing the trigger directly in SQL (editing) by manually removing the AssignedTo value.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.