MySQL Forums
Forum List  »  Triggers

Re: help!! with Secuence field (autonumeric)
Posted by: Ulf Wendel
Date: April 22, 2005 05:04AM

Ahoy!

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
> ERROR 1100 (HY000): Table 'ALM_PROVEEDORES' was
> not locked with LOCK TABLES
>
> what is the problem?
>
> thanks....
>
> CREATE TRIGGER TRG_PROV_BINSERT
> BEFORE INSERT ON ALM_PROVEEDORES
> FOR EACH ROW
> BEGIN
> DECLARE N INT;
> SELECT ifnull(max(PRO_CORReLATIVO),0)+1 INTO
> N FROM
> almacen.ALM_PROVEEDORES LIMIT 1;
>
> SET NEW.PRO_CORRELATIVO = N;
>
> END;

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.",
http://dev.mysql.com/tech-resources/articles/mysql-triggers.html

Ulf

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: help!! with Secuence field (autonumeric)
3792
April 22, 2005 05:04AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.