Solved

Help writing SQL Trigger

Posted on 2016-08-09
5
34 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 33

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 33
T-SQL: "HAVING CASE" Clause 1 27
Query Help - MSSQL - Averages 5 27
Connecting to multiple databases to create a Dashboard 5 26
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

803 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