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.

Help us by providing the error that you see.

Pradip
– Pradip

2013-10-10 09:08:23 +00:00
Commented Oct 10, 2013 at 9:08 — Pradip
– Pradip, Commented Oct 10, 2013 at 9:08

Przemyslaw Kruglej · Accepted Answer · 2013-10-10 09:09:16Z

2

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?

answered Oct 10, 2013 at 9:09

Przemyslaw Kruglej

8,1412 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

fancyPants · Accepted Answer · 2013-10-10 09:21:14Z

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.

Collectives™ on Stack Overflow

SQL - TRIGGER AFTER INSERT

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related