Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 268
  • Last Modified:

Help for a trigger newbie.

I have a company table which has person_id(int) and a current_company(bit) fields.  How can I create a trigger to ensure that the current_company bit set to 1 is unique for a person.  I think the best way is to take the person_id (@person_id) from the row that is being inserted or updated, and then

update person_company
set current_company = 0
where person_id = @person_id and current_company = 1

but I am unfamiliar with triggers and their syntax.

Any ideas?


This table basically holds employment history, so there may be multiple companies for a given person_id, but only one of them can be the current company.

To guarantee that this does not happen:

Person_id      Company_id      Current_Company      
12345            5345            1      
12345            678            1      
12345            345            0      
12345            789            0      
12345            436876            0      
12345            8766            0      
12345            76809            1      
12345            5757            0      

This is correct:
Person_id      Company_id      Current_Company      
12345            5345            0      
12345            678            0      
12345            345            0      
12345            789            0      
12345            436876            0      
12345            8766            0      
12345            76809            1      
12345            5757            0      
0
abenage
Asked:
abenage
  • 2
  • 2
1 Solution
 
BillAn1Commented:
here's an example of what you can do, depending on your exact requirements.
This trigger will update all records for the person, setting the current_company to 0 for all other records,
provided that the current record is set to 1. If the current record being inserted/ updated has a value
of 0 for the current_company, then the other records are left alone.
This trigger will allow you to ensure there is only one record with a current_company = 1 for a
given employee.
Note, the trigger will not work if you somehow update more than one record for an employee to 1 at the same time
If that happens, then the trigger will have the effect they will all cancel eachother out, so there will be no records with 1 left.
- is this a case you need to code for?



create trigger MyTrigger on person_company
for insert, update
as
      update person_company
      set current_company = 0
      from inserted i
      where person_company.person_id = i.person_id
      and person_company.company_id <> i.company_id
      and i.current_company = 1
0
 
Scott PletcherSenior DBACommented:
OOPS, I had actually posted a follow-up with trigger code already in the original q:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21413799.html#13936091
0
 
BillAn1Commented:
sorry Scott, I hadn't seen the previous Q.
looks like this really a duplicate question?(it's definitely a duplicate answer anyway :-)
0
 
Scott PletcherSenior DBACommented:
Np.  Yeah, it's a duplicate in a way, but an *innocent* one; you posted your answer very promptly and in good faith.  As far as I'm concerned, things are OK as they stand.  It also helped provide a second example of a trigger for the poster, who also got confirmation of the correct general code and approach :-) .


>> (it's definitely a duplicate answer anyway :-)

Except that, now that I think about it, your INNER JOIN is better than my [RIGHT] OUTER JOIN, since the WHERE clauses make the OUTER join unnecessary.
0
 
abenageAuthor Commented:
Thanks for the examples; all of them were very helpful as I have never experimented with triggers.

 I originally posted (http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21413799.html#13936091) about check constraints and learned that I needed to use triggers.  Since the ScottPletcher educated me about the limitations of check constraints, I gave him the points for the original question and then reposted, properly inquiring about triggers, where BillAn1 picked up on the new points.

Regardless, all of the information was very insightful.

Thanks again,
Aaron
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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