Var in Subquery Problem

xX_QueAnw_Xx · #1 (**permalink**) 03-17-11, 17:26

Hey,
I try to write an trigger which generates an ID for me. Unfortunately it throws an error. I think it's because of the var in the subquery

here my sourcecode:

Code:

DELIMITER #;
CREATE TRIGGER bi_user_id
BEFORE INSERT ON user
FOR EACH ROW

BEGIN
DECLARE i,ie INTEGER DEFAULT 0;
DECLARE leer CHAR(16) DEFAULT 'True';

SELECT count(*) INTO i FROM user;
SELECT ID INTO ie FROM user WHERE ID=(SELECT i);
IF ie>0 THEN
SELECT * FROM user;
ELSE
SELECT leer;
END#

The actions that are taken in the IF ELSE part are just test because I dont have an Backup of this Table and I dont want anything to come wrong.

sincerelly.

it-iss.com · #2 (**permalink**) 03-18-11, 03:40

Hi,

Firstly, a trigger is something that runs in the background as data is added, deleted or updated in a table. A trigger should never return any rows. This is just not permitted. I will assume that you are looking to create a stored procedure. There are several things that you should look closely at with this procedure:

From a functional perspective, if the ID's are consecutive without any gaps then this will work. Have you considered looking for the MAX(ID) instead?
There is no exception handling in the event that the query does not find a record.

I would propose something as follows:

Code:

CREATE PROCEDURE spUser()
BEGIN
  DECLARE leer CHAR(16) DEFAULT 'True';

  DECLARE ie INT;

  DECLARE CONTINUE HANDLER
  FOR NOT FOUND
    SET leer = 'True';

  SELECT id
  INTO   ie
  FROM   user
  WHERE  id = (SELECT Max(id)
               FROM   user);

  IF ie > 0 THEN
    SELECT *
    FROM   user;
  ELSE
    SELECT leer;
  END IF;
END;

Internet Services

Web Development

Digital Marketing

Consumer Tech