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