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;
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.
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 -> //
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
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;
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;
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.
Thanks Scott for yet another rescue. I owe you one my friend.
ReplyDeleteFrank
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
ReplyDeleteCREATE 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?
ReplyDeleteKez,
ReplyDeleteI 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?
ReplyDeleteSay 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).
ReplyDeleteHi,
ReplyDeleteHow do I create a trigger with multiple lines of sql in the FOR EACH ROW BEGIN... END block.
mysql> delimiter //
ReplyDeletemysql> 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!
ReplyDeletecan anybody please help me? I'm getting mad, my trigger is:
ReplyDeleteconsulta 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:
ReplyDeleteCREATE 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?
ReplyDeletecreate 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
ReplyDeletehi, 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.
ReplyDeleteCREATE 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:
ReplyDeletehttp://no-suelo.blogspot.com/2010/09/how-to-create-mysql-trigger.html
Regards
can anyone help me out with something.
ReplyDeletei 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.