From: Martijn Tonies
Date: December 5 2008 6:03pm
Subject: Re: Trying to Create a Trigger
List-Archive: http://lists.mysql.com/mysql/215490
Message-Id: <02d501c95703$c7c71040$9c02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	format=flowed;
	charset="utf-8";
	reply-type=response
Content-Transfer-Encoding: 7bit

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

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.

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