Hi,

I am newbie to SQL. I am trying to create a trigger on table X , which tests rowcount (with a condition) on table Y, before inserting data into X.

Eg, If rowcount with a condition of Y is greater than 1, it should NOT insert into table X

I tried with following, but its still inserting data into 'temp'

create trigger trigger_on_temp before insert on temp
for each row begin
if (select count(*) from temp2 where col='value') > 1 then
return false;
end if;
end;

Could anyone plz see where i am doing it wrong.

Thnx in advance.

Sandeep

What is "value" in your SQL statement?

Thnx for response

From further search on Internet, i learned 'returns' are not supported in Triggers.

Here is the clearer description:

Create a trigger on table X , which compares "count(*) on table X" with "a value from table Y", (before insert trigger on table X)

It goes something like this……..

create trigger trigger_on_X before insert on X
for each row begin
if (select count(*) from X where unitAddr='123' ) > (select MaxNumberOfPhones from Y) then
return false;
end if;
end;

# I should not do any further operation as above condition is "should never occur" type. I mean, it should not go ahead and insert data into table X. But next SQL insert succeeded.

I am trying to find a way where It SQL will not do any furtuer inserts.

With above trigger, it is inserting into table which it should not

Thnx,
Sandeep

Having done a bit of research it would appear that you cannot STOP a row from being inserted unless it is inserting invalid values. Therefore the only way to stop a record from being put in is to alter a NEW.value to NULL and then insert the NULL value into a non-null column. This best done with the primary key.

Bear in mind depending on how your MySQL server is set up it might try and truncate a value to it's nearest available possibility. With strict mode off if you are inserting a single row at a time you will find it fails (as you want it to), however on multi-inserts it will default the value to the nearest valid option. In strict mode this doesn't matter as it will fails for multi-inserts and single inserts.

I'm not 100% on how triggers deal with multi-inserts as opposed to single-inserts, but it's something you can try out to get your result.

Hope that helps...