• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 44
  • Last Modified:

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
0
Cobra967
Asked:
Cobra967
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now