Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    2

    Unanswered: Create Trigger help required

    Hi,

    I am newbie to SQL. I am trying to create a trigger on table X , which tests rowcount (with a condition) on table Y, before inserting data into X.

    Eg, If rowcount with a condition of Y is greater than 1, it should NOT insert into table X

    I tried with following, but its still inserting data into 'temp'

    create trigger trigger_on_temp before insert on temp
    for each row begin
    if (select count(*) from temp2 where col='value') > 1 then
    return false;
    end if;
    end;

    Could anyone plz see where i am doing it wrong.

    Thnx in advance.

    Sandeep

  2. #2
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    What is "value" in your SQL statement?

  3. #3
    Join Date
    Apr 2007
    Posts
    2

    Detailed description

    Thnx for response

    From further search on Internet, i learned 'returns' are not supported in Triggers.

    Here is the clearer description:

    Create a trigger on table X , which compares "count(*) on table X" with "a value from table Y", (before insert trigger on table X)

    It goes something like this……..

    create trigger trigger_on_X before insert on X
    for each row begin
    if (select count(*) from X where unitAddr='123' ) > (select MaxNumberOfPhones from Y) then
    return false;
    end if;
    end;

    # I should not do any further operation as above condition is "should never occur" type. I mean, it should not go ahead and insert data into table X. But next SQL insert succeeded.

    I am trying to find a way where It SQL will not do any furtuer inserts.

    With above trigger, it is inserting into table which it should not

    Thnx,
    Sandeep

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Having done a bit of research it would appear that you cannot STOP a row from being inserted unless it is inserting invalid values. Therefore the only way to stop a record from being put in is to alter a NEW.value to NULL and then insert the NULL value into a non-null column. This best done with the primary key.

    Bear in mind depending on how your MySQL server is set up it might try and truncate a value to it's nearest available possibility. With strict mode off if you are inserting a single row at a time you will find it fails (as you want it to), however on multi-inserts it will default the value to the nearest valid option. In strict mode this doesn't matter as it will fails for multi-inserts and single inserts.

    I'm not 100% on how triggers deal with multi-inserts as opposed to single-inserts, but it's something you can try out to get your result.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Code:
    CREATE TRIGGER trigger_on_X 
    BEFORE INSERT
    ON X
      FOR EACH ROW
        BEGIN
          IF (SELECT count(*) FROM X WHERE unitAddr='123' ) > (SELECT MaxNumberOfPhones from Y) THEN
            NEW.column1 = NULL;
          END IF;
        END;
    Hope that helps...

Posting Permissions

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