I am using MySQL 5.1 to write a Before Insert trigger. For this I want to check the receipt_no column and increment version_no if receipt_no is already in the table.
And I presume if no rows are placed using SELECT INTO this trigger fails which is what I want so the table which has default 0 default value on the column comes through.
But the version no seems to get duplicated. What seems like the problem here?
Table layout is of the form:
`id - auto incr primary key
receipt_no - unique constraint |
version_no - unique constraint |
other unrelevant column`
CREATE TRIGGER `increment_version` BEFORE INSERT ON `my_table` FOR EACH ROW BEGIN
DECLARE version INTEGER;
SELECT version_no INTO version FROM my_table WHERE receipt_no = NEW.receipt_no LIMIT 1;
SET NEW.version_no = version + 1;
END
If there is no record, then the query returns null into version, and NULL + 1 gives NULL.
You need to check whether version is null or not null.
Also use MAX in the query, because you need to retrieve a greatest version number.
CREATE TRIGGER `increment_version`
BEFORE INSERT ON `my_table` FOR EACH ROW
BEGIN
DECLARE version INTEGER;
SELECT max(version_no) INTO version FROM my_table
WHERE receipt_no = NEW.receipt_no
;
IF version IS NOT NULL THEN
SET NEW.version_no = version + 1;
END IF;
END
see this demo: http://www.sqlfiddle.com/#!2/b82c34/1
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments