Solved

Help writing SQL Trigger

Posted on 2016-08-09
5
28 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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 142

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 32

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now