Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have two tables:

  • service (id_service, libel, status)
  • agent (id_agent, name, surname, id_service, status).

The status field values ​​present in both tables have only two values ​​0 or 1 (0 = inactive and 1 = active).

I want that when the field status of the department table is updated (i.e. if it is set to 0), the status of the agent table field is automatically set to 0 as well.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

You can use a trigger to achieve this. Have a look at the following example of how to do this:

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE service (id_service INT, libel INT, status INT);
CREATE TABLE agent (id_agent INT, name VARCHAR(10), surname VARCHAR(10), id_service INT, status INT);
GO
CREATE TRIGGER service_update AFTER UPDATE ON service 
FOR EACH ROW
BEGIN
  UPDATE agent SET status = NEW.status WHERE id_service = NEW.id_service;

END
GO

CREATE PROCEDURE testupdate 
  (p_id_service INT,p_status INT)
BEGIN
  UPDATE service SET status = p_status WHERE id_service = p_id_service;

  SELECT s.id_service, s.status service_status, a.id_agent, a.status agent_status
  FROM service s
  JOIN agent a
  ON s.id_service = a.id_service;
END
GO


INSERT INTO service VALUES(1,0,0);
INSERT INTO service VALUES(2,0,0);

INSERT INTO agent VALUES(1,'a','a',1,0);
INSERT INTO agent VALUES(2,'b','b',2,0);
INSERT INTO agent VALUES(3,'c','c',2,0);

Query 1:

CALL testupdate(2,1);

Results:

| ID_SERVICE | SERVICE_STATUS | ID_AGENT | AGENT_STATUS |
|------------|----------------|----------|--------------|
|          1 |              0 |        1 |            0 |
|          2 |              1 |        2 |            1 |
|          2 |              1 |        3 |            1 |

Query 2:

CALL testupdate(1,-1);

Results:

| ID_SERVICE | SERVICE_STATUS | ID_AGENT | AGENT_STATUS |
|------------|----------------|----------|--------------|
|          1 |             -1 |        1 |           -1 |
|          2 |              1 |        2 |            1 |
|          2 |              1 |        3 |            1 |

Query 3:

CALL testupdate(2,0);

Results:

| ID_SERVICE | SERVICE_STATUS | ID_AGENT | AGENT_STATUS |
|------------|----------------|----------|--------------|
|          1 |             -1 |        1 |           -1 |
|          2 |              0 |        2 |            0 |
|          2 |              0 |        3 |            0 |

(Don't be confused by the GO statements. They are used as batch separators in SQLFiddle and are not sent to MySQL.)

However, this seems to be a problem with your schema. If both status columns need to be updated together they do not have separate meaning, so there probably should be only one status.

If agents can be disabled without affecting the service, then you should check to see if the service is disabled before starting an agent and not rely on the status having been copied correctly to the agent.

Consider for example the case where the service got disabled causing all agents to be disabled. If now someone enables a single agent you have the system in an invalid state. It is better to not duplicate the information in this case. You can use a select statement similar to this to get to the status of the service that belongs to a specific agent - without duplicating the information:

SELECT s.id_service, s.status service_status, a.id_agent, a.status agent_status
  FROM service s
  JOIN agent a
  ON s.id_service = a.id_service
 WHERE a.id_agent = 3;
share|improve this answer
add comment

You can add an update trigger on service(department) table like this:

CREATE TRIGGER dbo.trg_service_update ON dbo.service
AFTER UPDATE
AS
BEGIN
    UPDATE ag
    SET ag.STATUS = i.STATUS
    FROM INSERTED i
    INNER JOIN dbo.agent ag ON i.id_service = ag.id_service
END
share|improve this answer
1  
I don't think that is valid MySQL syntax. –  a_horse_with_no_name Sep 16 '13 at 15:54
    
This is SQL Server syntax. The question is about MySQL. –  Sebastian Meine Sep 16 '13 at 18:31
    
I tried this with mysql CREATE TRIGGER service_update BEFORE UPDATE ON service FOR EACH ROW BEGIN IF New.Statut <>Old.Statut THEN update agent set agent.Statut=0 END IF END –  soltic Sep 16 '13 at 19:16
    
hi i'll test it and turn back about the results –  soltic Sep 16 '13 at 19:53
    
Consider this as a pseudo code, as i have tried just to explain the concept and Sebastian has implemented the same concept - update trigger. –  masam Sep 16 '13 at 21:49
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.