Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help writing SQL Trigger

Posted on 2016-08-09
5
Medium Priority
?
42 Views
Last Modified: 2016-08-09
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
Comment
Question by:Cobra967
  • 2
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 41748644
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
 

Author Comment

by:Cobra967
ID: 41748684
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 41748714
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
 
LVL 36

Expert Comment

by:ste5an
ID: 41748719
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
 

Author Closing Comment

by:Cobra967
ID: 41748848
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

879 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question