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.
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?
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.
