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

How to create a T-SQL Trigger to update a table

I have a Helpdesk package that runs on SQL 2005. When a new email comes into the Helpdesk package I must manually assign the call to IT managers in branch offices. Can I create a trigger to automatically assign a ticket to an IT manager depending on the email domain of the sender?

There is a table called tbl_issue in which a new row is created each time a new ticket is generated, a field containing the email address of the sender and a field containing a number relating to an  IT Manager the ticket has been assigned to. A table named tbl_ref contains the name of the IT Managers. A field name ID contained in tbl_ref links to fld_aref in tbl_issue.

I'm spending a good 4 hours a day manually assigning calls so, it would be a great if someone can help me automate this task.

0
foxc51
Asked:
foxc51
  • 5
  • 4
1 Solution
 
chapmandewCommented:
Can you provide a couple of lines of sample data from your tables so I can visually see how they relate?  From there I can probably write the trigger for you.
0
 
foxc51Author Commented:
Hi, attached is an Excel sheet with the tables in.
Helpdesk-Tables.xls
0
 
chapmandewCommented:
I don't see the field named fld_aref in tbl_issue
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
foxc51Author Commented:
Sorry, should be fld_repid.
0
 
chapmandewCommented:
OK...to make sure I understand this right.

The two records in tbl_issue haven't been assigned yet.  Youd like for them to be assigned (based on the domain in fldEmail) to a user in tbl_ref.  Does that sound right?
0
 
foxc51Author Commented:
Yes, that's what I am hoping.
0
 
chapmandewCommented:
OK..give this one a shot.  This will pick the first ID from the ref table and assign it to the ticket if the domains from the email fields match up.  


create trigger tr_tblissue on tbl_issue for insert
as
begin
 
update u
set fldREP_ID = 
(
	select max(id) from tbl_ref r
	where substring(r.fldEmail, charindex('@',r.fldEmail)+1, charindex('.', r.fldEmail, charindex('@',r.fldEmail)) - charindex('@',r.fldEmail)-1) = 
	substring(u.fldEmail, charindex('@',u.fldEmail)+1, charindex('.', u.fldEmail, charindex('@',u.fldEmail)) - charindex('@',u.fldEmail)-1)
 )
from tbl_issue u
join inserted i on u.id = i.id
 
end

Open in new window

0
 
foxc51Author Commented:
OK. Works! However, this there a way to start reading the tbl_ref from record 100?
0
 
chapmandewCommented:
sure, I just added some criteria to the where clause of the subquery

create trigger tr_tblissue on tbl_issue for insert
as
begin
 
update u
set fldREP_ID =
(
        select max(id) from tbl_ref r
        where substring(r.fldEmail, charindex('@',r.fldEmail)+1, charindex('.', r.fldEmail, charindex('@',r.fldEmail)) - charindex('@',r.fldEmail)-1) =
        substring(u.fldEmail, charindex('@',u.fldEmail)+1, charindex('.', u.fldEmail, charindex('@',u.fldEmail)) - charindex('@',u.fldEmail)-1) and ID > 100
 )
from tbl_issue u
join inserted i on u.id = i.id
 
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

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