List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:December 5 2008 7:22pm
Subject:Re: Trying to Create a Trigger
View as plain text  
 >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
>
>   

Thread
Trying to Create a TriggerLola J. Lee Beno5 Dec
  • Re: Trying to Create a TriggerDavid Giragosian5 Dec
    • Re: Trying to Create a TriggerLola J. Lee Beno5 Dec
      • Re: Trying to Create a TriggerJim Lyons5 Dec
        • Re: Trying to Create a TriggerLola J. Lee Beno5 Dec
  • Re: Trying to Create a TriggerMartijn Tonies5 Dec
    • Re: Trying to Create a TriggerLola J. Lee Beno5 Dec
  • Re: Trying to Create a TriggerMartijn Tonies5 Dec
    • Re: Trying to Create a TriggerLola J. Lee Beno5 Dec
      • Re: Trying to Create a TriggerAndy Shellam5 Dec
        • Re: Trying to Create a TriggerLola J. Lee Beno5 Dec
          • Re: Trying to Create a TriggerPeter Brawley5 Dec
  • Re: Trying to Create a TriggerMartijn Tonies5 Dec
    • Re: Trying to Create a TriggerWm Mussatto5 Dec
      • Re: Trying to Create a TriggerAndy Shellam6 Dec
  • Re: Trying to Create a TriggerMartijn Tonies6 Dec