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
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
but it didn't work.
Is it possible or am I shooting in the dark here?
Thanks
Frank
I tried something like
but it didn't work.
Is it possible or am I shooting in the dark here?
Thanks
Frank
Subscribe to:
Post Comments (Atom)
16 comments:
Thanks Scott for yet another rescue. I owe you one my friend.
Frank
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
Can MySQL triggers execute a shell command by any chance?
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
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?
Triggers are allowed in MySQL, but their performance is a joke. It's hardly a serious implementation yet (as of 5.1).
Hi,
How do I create a trigger with multiple lines of sql in the FOR EACH ROW BEGIN... END block.
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 !!
can mysql trigger update a table from another db? if so, do you know what is the impact to the performace? Thx!
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 ;
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
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;
Hello....help me please!!! How can i impliment partisipation constraint in MYSql od SQL
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;
Here, a Trigger Syntax in MySQL:
http://no-suelo.blogspot.com/2010/09/how-to-create-mysql-trigger.html
Regards
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.
Post a Comment