Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    1

    Unanswered: Var in Subquery Problem

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •