From: Peter Brawley
Date: December 5 2008 7:22pm
Subject: Re: Trying to Create a Trigger
List-Archive: http://lists.mysql.com/mysql/215494
Message-Id: <49397F61.4000400@earthlink.net>
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------020904030003070304020908"

--------------020904030003070304020908
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

 >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
>
>   

--------------020904030003070304020908--