axel lope wrote:
> i try to create an autonumeric field, with select
> max for the pk field
Why? There's auto_increment, http://dev.mysql.com/doc/mysql/en/example-auto-increment.html
> but when y try to insert in this table return this
> ERROR 1100 (HY000): Table 'ALM_PROVEEDORES' was
> not locked with LOCK TABLES
> what is the problem?
> CREATE TRIGGER TRG_PROV_BINSERT
> BEFORE INSERT ON ALM_PROVEEDORES
> FOR EACH ROW
> DECLARE N INT;
> SELECT ifnull(max(PRO_CORReLATIVO),0)+1 INTO
> N FROM
> almacen.ALM_PROVEEDORES LIMIT 1;
> SET NEW.PRO_CORRELATIVO = N;
Check for "SELECT FROM TABLE" in the following quote:
"I assume that you have read the first book in the “MySQL New Features” series already. In that book, “MySQL Stored Procedures”, you (I hope) saw how MySQL supports stored procedures and functions. That's important knowledge, because you can use the same statements in triggers as you can use in functions. Specifically:
• Compound statements (BEGIN / END) are legal.
• Flow-of-control statements (IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE, ITERATE) are legal.
• Variable declaration (DECLARE) and assignment (SET) are legal.
• Condition declarations are legal.
• Handler declarations are legal.
But remember that functions are subject to severe limitations: you cannot access tables from within a function. So these statements are illegal inside a function:
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE
Precisely the same limitation applies for triggers.",