what's wrong with this trigger??

[network18]

what;s wrong with this mysql trigger logically??

DELIMITER |

CREATE TRIGGER comment_count AFTER INSERT ON comments
 FOR EACH ROW
 BEGIN

UPDATE sub_sections as t1 SET t1.published_comment_count = (select count(id) as cnt from comments where article_id=t1.id and published=0 and status=0)
WHERE id= (select article_id from comments order by date_modified desc limit 0,1 );
END;

DELIMITER ;

[tesuji]

Just out of curiosity: Is there also an error message or faulty behavior of that code?

Supposing your update and subselects are correct, I would replace END; by END | for you have chosen delimiter |

-- tesu

[network18]

Just out of curiosity: Is there also an error message or faulty behavior of that code?

Supposing your update and subselects are correct, I would replace END; by END | for you have chosen delimiter |

-- tesu

though syntactically its not wrong, lets improve with the below -

delimiter |

create trigger comment_count AFTER INSERT on comments
FOR EACH ROW 
BEGIN

UPDATE sub_sections as t1 SET t1.published_comment_count = (select count(id) as cnt from comments where article_id=t1.id and published=0 and status=0)
WHERE id= (select article_id from comments order by date_modified desc limit 0,1 );
END;

|

delimiter ;

Note the use of redefined delimiter for end of the trigger.
During the tests it worked as desired, but in the sub query t1.id is logically supcious.

[tesuji]

Hi

This trigger is invoked after a row has been inserted in table comments. Within the trigger you can access all just inserted column-values by NEW.column_name, for example NEW.article_id ist the article_id of the row just inserted.

>> where article_id=t1.id and
If article_id is from the trigger-invoking table comments, use: where NEW.article_id=sub_sections.id and.
I have also replaced alias t1 by table name sub_sections for mysql has a rather weird notion of aliases in where-clause.

>> WHERE id= (select article_id
If you want to get the article_id which has been just inserted, use: WHERE sub_sections.id= NEW.article_id

As for the select count(id)... I am really not sure whether you will already have access to table comments on this conventional way by using select etc because you are in the trigger which is invoked by inserting a row in that table. You could test it with a simpler trigger.

You may also consider that on older versions of mysql (before 5.1?) the user who creates triggers must have super privilege. If not, behaviour of invoked triggers are indeterminate.

-- tesu

[tesuji]

Hello again

The following method for counting occurrences in a table and storing the result in another table works fine on my mysql version 5.1.48:

create trigger comment_count AFTER INSERT on comments
FOR EACH ROW 
BEGIN
declare cnt int;
select count(id) into cnt from comments where article_id=t1.id and published=0 and status=0;
UPDATE sub_sections as t1 SET t1.published_comment_count = cnt
  WHERE id= NEW.article_id;
END;

The into-clause is only allowed within stored procedures&functions and in triggers or in embedded SQL. Within triggers you can make use of almost all features of stored procedures and functions as explained in 12.7. MySQL Compound-Statement Syntax of 5.1 manual. These are true PSM programming language features as standardized in SQL 1999/2003.

I have also used NEW.article_id instead of subselect for NEW selects the just inserted value of column article_id from table comments.

The trigger can also count occurrences in the table which just invoked the trigger itself, there is no restriction (as I had supposed).

-- tesu

[Morta]

Hiii, i found something near WHERE id= ... ,use t1.id unsteaf of id and the operator in :

delimiter |

create trigger comment_count AFTER INSERT on comments
FOR EACH ROW 
BEGIN

UPDATE sub_sections as t1 SET t1.published_comment_count = (select count(id) as cnt from comments where article_id=t1.id and published=0 and status=0)
WHERE t1.id in (select article_id from comments order by date_modified desc limit 0,1 );
END;

|

delimiter ;

i think this code is better too:

create trigger comment_count AFTER INSERT on comments
FOR EACH ROW 
BEGIN
declare cnt int;
select count(id) into cnt from comments where article_id=t1.id and published=0 and status=0;
UPDATE sub_sections as t1 SET t1.published_comment_count = cnt
  WHERE id= NEW.article_id;
END;

%^^%Murtada%^^%