Mysql trigger using parameters

[don2241]

Hi
Is it possible to use a trigger on a table to update a different table using parameters?

TableA
user | id | data | duration
10 1 aaa 200

TableB
user | id | totDuration
10 1 null

What I am trying to do is to have a trigger on TableA when an UPDATE takes place to update totDuration column in TableB. I will try to explain using a trigger creation that obviously do not work but maybe you can see what I am trying to do.

create trigger trigger_name after update on TableA
for each row
begin
declare @user integer;
declare @id integer;
set @user = [user from TableA]
set @id = [id from TableA]

update TableB
set duration=duration + [duration from TableA]
where
user=@user
and id=@id
end

What I should end up with in the duration column in TableB is 200. If I do another update with the same value I should end up with 400 and so forth.

I am using Mysql 5.0.2

Thank you
/M

 

[ingresman]

have you tried ?

 

[don2241]

Yes of course I tried, I would not be posting here if I did not.
The error msg I get is:

**********************
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 '@user integer;
declare @id integer;
at line 4
***********************

 

[ingresman]

with all due respect, you did ask the question is it possible and you didn't indicate that you tried and got a syntax error not a run time ot semantic error.

 

[don2241]

Don't worry about it ingresman, with monologs the information does not always come across the way you intended.

Anyhow, I found out the problem. A simple one, it always is, you cannot use declare syntax in mysql triggers.

/M