Query help

[JasonMcConnell]

Hi,

I am new to SQL and have created the following trigger in MS SQL Express but can't get it to work in MYSQL 5.0

Can anybody tell me what is wrong it say the sytanx is wrong

create trigger totalcost_c
on contacts_cstm
for insert,update as
if update (hatcost_c)
or update (shipping_c)
begin
update contacts_cstm
set totalcost_c = (hatcost_c + shipping_c)
end

Thanks for youe help

 

[JasonMcConnell]

This is the error I am getting #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on contacts_cstm for insert,update as if update (hatcost_c) or update (ship' at line 2

 

[DonQuichote]

See

http://dev.mysql.com/doc/refman/5.1/en/triggers.html

(the fine manual). The Syntax page contains a few nice examples.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[tsuji]

Mssql syntax certainly won't make a easy transition to mysql's. You would have to make two triggers at least one for update and one for insert, separately. Adding to the finer detail of update on specific fields, they would look slightly different all the same. Here is a possible realization.
[tt]
CREATE TRIGGER totalcost_c_insert
BEFORE INSERT
ON contacts_cstm
FOR EACH ROW
BEGIN
SET NEW.totalcost_c = (NEW.hatcost_c + NEW.shipping_c);
END;

CREATE TRIGGER totalcost_c_update
BEFORE UPDATE
ON contacts_cstm
FOR EACH ROW
BEGIN
IF (NEW.hatcost_c<>OLD.hatcost_c) OR (NEW.shipping_c<>OLD.shipping_c) THEN
SET NEW.totalcost_c = (NEW.hatcost_c + NEW.shipping_c);
END IF;
END;
[/tt]
That would be about right to take into account of all finer requirements on the update.