Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to write a Trigger to register every new inserted genre into a news table.

Here are the tables definitions. They are only two, GENEROS & NOVEDADES.

This is the trigger:

CREATE TRIGGER insertarNovedades AFTER INSERT ON GENEROS
FOR EACH ROW 
    INSERT INTO NOVEDADES (novedad) VALUES (CONCAT('Nuevo genero: ', genero));
$$

But MySQL don't let me use it:

mysql> INSERT INTO GENEROS (genero)VALUES('f');
ERROR 1054 (42S22): Unknown column 'genero' in 'field list'

What is wrong on the trigger code?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

This works:

CREATE TRIGGER insertarNovedades AFTER INSERT ON GENEROS
FOR EACH ROW
    INSERT INTO NOVEDADES (novedad) VALUES (CONCAT('Nuevo genero: ', new.genero));

Manual has this example and the following explanation:

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
     -> FOR EACH ROW SET @sum = @sum + NEW.amount; Query OK, 0 rows affected (0.06 sec)

The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event. In the example, the trigger body is a simple SET that accumulates into a user variable the values inserted into the amount column. The statement refers to the column as NEW.amount which means “the value of the amount column to be inserted into the new row.”

share|improve this answer
    
I would like to know why the hell is necessary to use NEW when is the only choice? That should be the default, no need for write it explicitly. –  Lucio Jun 14 at 2:17
2  
@Lucio Explicit is better than implicit. NEW may not always be the only choice. You may have INSERT ... SELECT ... FROM there or UPDATE ... FROM, with possibly more than one tables in the FROM clause. Then there may be a genero column in multiple tables besides the NEW (pseudo-table). –  ypercube Jun 14 at 19:27
    
@ypercube Sorry, I missed that part. I'm not sure, but are you saying something like this? TRIGGER...INSERT...FOR EACH ROW INSERT INTO GENEROS (genero) VALUES(NEW.genero) If that is possible, then yeah, the NEW keyword would be necessary. –  Lucio Jun 14 at 19:41
    
There are also UPDATE triggers where there would be both a NEW.genero and an OLD.genero column. Your suggestion would not work there. –  ypercube Jun 14 at 19:41
1  
No, I meant things like TRIGGER...INSERT...FOR EACH ROW INSERT INTO novedades (..columns..) SELECT ... FROM another_table_or_tables ; –  ypercube Jun 14 at 19:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.