SQL - TRIGGER AFTER INSERT

Question

I have two tables - 'clients' and 'clients_address'

There is on table 'clients' column with name 'phone'. I need to have the same column 'phone' in table 'clients_address'

I create column 'phone' in table 'clients_address' and whit this command I copied the data from 'clients.phone':

UPDATE clients_addresses
SET clients_addresses.phone=(SELECT clients.phone
FROM clients
WHERE clients.id=clients_addresses.client_id);

---

Everything is copied correctly, but it is not autoupdate. And after new user registration i need to execute this command again. I try with trigger, but SQL return me sintax error. This is what I try:

CREATE TRIGGER up
ON clients.phone
AFTER INSERT 
BEGIN
UPDATE clients_addresses
SET clients_addresses.phone=(SELECT clients.phone
FROM clients
WHERE clients.id=clients_addresses.client_id)
END;

---

I am not very good in sql. Please help.

Answer 1

Try the following:

CREATE TRIGGER up
AFTER INSERT
  ON clients
  FOR EACH ROW
BEGIN
  UPDATE clients_addresses
    SET clients_addresses.phone = :new.phone
  WHERE clients_addresses.client_id = :new.id;
END;

What database are you using?

Answer 2

What you're missing is a ; after your update statement. This would end the trigger definition, though. To indicate that the ; finishes the update statement but not the trigger definition, you have to change the delimiter first.

DELIMITER $$
CREATE TRIGGER up
ON clients.phone
AFTER INSERT 
BEGIN
UPDATE clients_addresses
SET clients_addresses.phone=(SELECT clients.phone
FROM clients
WHERE clients.id=clients_addresses.client_id);
END $$
DELIMITER ;

After that we change the delimiter back to the standard ;. Apart from that, I'd recommend to use a join instead of a subquery. Also you want to update just the phone of the client you just inserted, not the whole table. You missed that in your WHERE clause.

DELIMITER $$
CREATE TRIGGER up
ON clients.phone
AFTER INSERT 
BEGIN
UPDATE clients_addresses ca
INNER JOIN clients c ON c.id = ca.client_id
SET ca.phone = c.phone
WHERE c.id = NEW.id;
END $$
DELIMITER ;

You didn't mention which RDBMS you are using. There might be differences to the correct statement. For example, if you're using MySQL you're missing a FOR EACH ROW in there. And please, always post the error message you got along with the question.