Re: Trying to Create a Trigger
Going back to the OP's problem - the original issue I believe was he was using old instead of OLD (case-sensitive) - now that's sorted, MySQL is complaining about a syntax error toward the end of the function declaration. I'm surprised by the case sensitivity of OLD though, it works fine on my Windows system. If this is really it... then ugh... Case sensitivity on object names has to be one of the most retarted things ever... With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
On 12/5/08, Lola J. Lee Beno [EMAIL PROTECTED] wrote: I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record end if; end // delimiter ; When I run the set query without the and old.jobpost_id line, it runs correctly. So the syntax problem is elsewhere, but where? I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David
Re: Trying to Create a Trigger
David Giragosian wrote: I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David No . . . 'old' is a virtual table that is the same as the table I'm doing work on. See http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html. What I need to do is check if jobposts record has jobposts.adsource_id (foreign key) which still exists in adsource table (primary key). If adsource record still exists, then do not proceed with deleting jobpost record - that's what I'm trying to do and thus is what this trigger is supposed to do. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record end if; end // delimiter ; When I run the set query without the and old.jobpost_id line, it runs correctly. So the syntax problem is elsewhere, but where? What is the exact error message? With regards, Martijn Tonies Upscene Productions http://www.upscene.com Free Database Workbench Lite for MySQL at www.upscene.com ! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
can you not use referential integrity for this - assuming the tables are or can be made to be innodb? Does the jobposts table have a jobpost_id field, or is it just id? Maybe it's a typo? On Fri, Dec 5, 2008 at 11:28 AM, Lola J. Lee Beno [EMAIL PROTECTED] wrote: David Giragosian wrote: I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David No . . . 'old' is a virtual table that is the same as the table I'm doing work on. See http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html. What I need to do is check if jobposts record has jobposts.adsource_id (foreign key) which still exists in adsource table (primary key). If adsource record still exists, then do not proceed with deleting jobpost record - that's what I'm trying to do and thus is what this trigger is supposed to do. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Trying to Create a Trigger
Jim Lyons wrote: can you not use referential integrity for this - assuming the tables are or can be made to be innodb? The tables are myISAM. These could be changed to innodb but I want to see if i can get this trigger work. Does the jobposts table have a jobpost_id field, or is it just id? Maybe Yes. Jobpost_id. No, not a typo. It's primary key, jobposts.adsource_id is foreign key while adsource.adsource_id is primary key. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Martijn Tonies wrote: What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Query = set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ) It looks like I can't do what I want to do . . . get the jobpost_id to check jobpost and adsource tables BEFORE proceeding to delete the record. If so, what's the best way to do this? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) As a complete sidenote: It's better to write your JOINs with a JOIN clause and to put your strings inside single quotes as per SQL standard, double quotes are really for object names. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Martijn Tonies wrote: Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Now I just have this error message: ERROR: 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 'end // delimiter' at line 1 Query = end // delimiter As a complete sidenote: It's better to write your JOINs with a JOIN clause and to put your strings inside single quotes as per SQL standard, double quotes are really for object names. Right . . . I'll get the JOIN clause figured out after I figure out what's causing the above error message. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Now I just have this error message: ERROR: 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 'end // delimiter' at line 1 Query = end // delimiter I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Andy Shellam wrote: I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. That's not it, unfortunately. ERROR: 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 'if @counted = 1 then SET dummy = 'Cannot delete this record' end if' at line 1 Query = if @counted = 1 then SET dummy = 'Cannot delete this record' end if -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
ERROR: 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 'if @counted = 1 then SET dummy = 'Cannot delete this record' end if' at line 1 This works on 5.1.30 and 6.0.7: create table jobposts(adsource_id int,jobpost_id int); create table adsource(adsource_id int); delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = (select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; PB - Lola J. Lee Beno wrote: Andy Shellam wrote: I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. That's not it, unfortunately. ERROR: 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 'if @counted = 1 then SET dummy = 'Cannot delete this record' end if' at line 1 Query = if @counted = 1 then SET dummy = 'Cannot delete this record' end if Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.11/1820 - Release Date: 11/29/2008 6:52 PM
Re: Trying to Create a Trigger
Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Try: end; // delimiter ; I just wrote the trigger source in the Trigger Editor in Database Workbench and it doesn't bother about delimiters and such... It also has no ; after the final END, but hey, who knows ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
On Fri, December 5, 2008 12:14, Martijn Tonies wrote: Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Try: end; // delimiter ; I just wrote the trigger source in the Trigger Editor in Database Workbench and it doesn't bother about delimiters and such... It also has no ; after the final END, but hey, who knows ;-) With regards, Martijn Tonies Upscene Productions http://www.upscene.com I think you are missing the point. Where is 'OLD' or 'old' defined? Before you try to imbed it in a trigger, try the basic query. That seems to be what its complaining about. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
I think you are missing the point. Where is 'OLD' or 'old' defined? Before you try to imbed it in a trigger, try the basic query. That seems to be what its complaining about. OLD is a virtual table which is only present in a trigger - it's like a table with the same layout as the table the trigger is attached to, which contains the row (or rows) that were deleted (for a DELETE query) or updated (for an UPDATE query - pre-update.) Just the same as (IIRC) NEW is a virtual table that contains the row (or rows) that were inserted (for an INSERT query) or updated (for an UPDATE query - post-update.) The query in question will fail anywhere outside the trigger because NEW and OLD are only in scope in triggers. OLD is akin to the deleted virtual table in MS SQL Server triggers, just like NEW is akin to the inserted virtual table in MSSQL. Going back to the OP's problem - the original issue I believe was he was using old instead of OLD (case-sensitive) - now that's sorted, MySQL is complaining about a syntax error toward the end of the function declaration. Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
