Re: Trying to Create a Trigger

[Peter Brawley]

>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