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
Let me know if you need clarification in any of the pseudo steps
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 IfLet me know if you need clarification in any of the pseudo steps
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
if it's an empty string, this needs to be handled differently
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.
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.
ASKER
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.
ASKER