Tuesday, January 24, 2006

Can MySQL triggers update another table?

After playing earlier with triggers, I wanted to see whether one can update another table using MySQL triggers.

I tried something like
CREATE TRIGGER mytrigger BEFORE INSERT ON odp.points FOR EACH ROW SET total_points.points = total_points.points + NEW.points;

but it didn't work.

Is it possible or am I shooting in the dark here?

Thanks
Frank

17 comments:

Scott Noyes said...

Yes, it's possible. You just have to put proper SQL in the trigger:

CREATE TRIGGER mytrigger BEFORE INSERT ON odp FOR EACH ROW UPDATE total_points SET points = points + NEW.points;

Frankly Speaking! said...

Thanks Scott for yet another rescue. I owe you one my friend.

Frank

Anonymous said...

hello i am expereiencing a similar problem i want to update another table but the database will not allow me pleas help kez. i put this in

CREATE TRIGGER vehicle_update AFTER INSERT ON Hire_Vehicle
FOR EACH ROW
UPDATE Vehicle v, Hire_Vehicle hv
SET v.state_of_vehicle = 'NO HIRE'
FROM Hire_Vehicle hv, Vehicle v
WHERE v.regno = hv.regno;

and on inserting i get this error

you have an error in your mysql syntax

please help kez

Bhutz said...

Can MySQL triggers execute a shell command by any chance?

Anonymous said...

Kez,

I think the problem with your Vehicle_Update trigger is on the UPDATE line. You should be updating the single table Vehicle, not both of them.

You've probably figured this by now, but thought I'd post.

-davio

Erick said...

How do you associate the two tables?

Say TABLE1 has an a_id, and TABLEB has a b_id, both of which are one to one connection.

Now, I want to update TABLEB.b_id with the TABLEA.a_id when anything in TABLEA is updated. Any idea?

My CREATE TRIGGER syntax is correct, but the trigger doesn't work if I do it as "AFTER UPDATE". Only when I make it BEFORE UPDATE it works, and the UPDATE query takes about 8 seconds to execute.

Any thoughts on why?

Anonymous said...

Triggers are allowed in MySQL, but their performance is a joke. It's hardly a serious implementation yet (as of 5.1).

Dino said...

Hi,

How do I create a trigger with multiple lines of sql in the FOR EACH ROW BEGIN... END block.

Dino said...

mysql> delimiter //
mysql> CREATE TRIGGER updtrigger BEFORE UPDATE ON Employee
-> FOR EACH ROW
-> BEGIN
-> IF NEW.Salary<=500 THEN
-> SET NEW.Salary=10000;
-> ELSEIF NEW.Salary>500 THEN
-> SET NEW.Salary=15000;
-> END IF;
-> END
-> //

Got it !!

Anonymous said...

can mysql trigger update a table from another db? if so, do you know what is the impact to the performace? Thx!

Belka said...

can anybody please help me? I'm getting mad, my trigger is:
consulta SQL:

CREATE TRIGGER `adminmec2`.`trigger_asistencia_falta` AFTER INSERT ON `adminmec2`.`asistencia`
FOR EACH
ROW IF( NEW.`HRAINI` = NULL
OR NEW.`HRASAL` = NULL )
THEN
BEGIN
INSERT INTO `adminmec2`.`falta` (
`CODFAL` ,
`CODMEC` ,
`FECHAFALTA` ,
`HRAINIFAL` ,
`HRASALFAL`
)
VALUES (
NULL , NEW.`CODMEC`, NEW.`FECHAAS`, NEW.`HRAINI`, NEW.`HRASAL`
)
END ;

Anonymous said...

i paste it my trigger it showing Error:

CREATE TRIGGER update_mytable1
AFTER INSERT ON product1
FOR EACH ROW
BEGIN
UPDATE mytable1 t1,product1 t2 SET t1.ProductName=t2.ProductName WHERE t1.Ident=t2.Ident
END;

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 'UPDATE mytable1 t1,product1 t2 SET t1.ProductName=t2.ProductName+ NEW.ProductNam' at line 5

Pls help me any one

jenlincol said...

I am trying a similar update but get a syntax error trying to create the trigger. Can someone help?

create trigger updatesponsor after update on associates
for each row
begin
If (New.aLevel != OLD.aLevel) THEN
Update sponsor s
Set s.sLevel = New.aLevel
where old.Sponsor_ID = Sponsor_ID;
end if;

olga said...

Hello....help me please!!! How can i impliment partisipation constraint in MYSql od SQL

srithar said...

hi, I am new in mysql. Actually my problem is when update one particular field only trigger will fire. so i need syntax for that. Then based on above information i made query. But its shows an error.
CREATE TRIGGER S_2_UPDATE_Trigger Before update as if (s_2_password) on s_2 BEGIN INSERT INTO S_34 SET
S_34_ParentRecordID = OLD.S_2_ID ,
S_34_ModifiedBy = OLD.S_2_ModifiedBy , S_34_ModifiedDate = OLD.S_2_ModifiedDate, S_34_RecordedBy = OLD.S_2_RecordedBy,
S_34_RecordedDate = OLD.S_2_RecordedDate, S_34_TenantID = OLD.S_2_TenantID, S_34_VersionID = OLD.S_2_VersionID ,
s_34_username=OLD.s_2_username,s_34_password=OLD.s_2_password,s_34_EmailID=OLD.s_2_EmailID;
DELETE FROM S_34 WHERE S_34_ParentRecordID = OLD.S_2_ID and S_34_VersionID <= OLD.S_2_VersionID - 5;End if ;END;

Albert Asensio said...

Here, a Trigger Syntax in MySQL:

http://no-suelo.blogspot.com/2010/09/how-to-create-mysql-trigger.html

Regards

Anonymous said...

can anyone help me out with something.
i am using apex and i have a table called projects table which has different fields such as project name, project leader etc. i have another table called impact table which shows the impact of different projects on different systems(there is another systems table and the projects and systems are related to each other). what i want to do is that i want to use a trigger so that when i enter a new project int he projects table it should automatically add the project name to the impact table in the form of a column. the code i have written for it is:
create trigger impactupdatefromproject
after insert of PROJECT_NAME ON PMO_PROJECTS
begin
insert into PMO_SYSTEMS(SYSTEM_NAME) values('I',PROJECT_NAME);
end;
but this code is not working.
any help would be appreciated. thanks.