List:General Discussion« Previous MessageNext Message »
From:Lola J. Lee Beno Date:December 5 2008 6:24pm
Subject:Re: Trying to Create a Trigger
View as plain text  
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)

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