#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0

    Why doesn't my trigger work?


    I am trying to define a trigger in which if inserted email is null, change the Email to(a-Mail address blocked: See forum rules), where xxx
    is the SNb.
    This is the table:
    Students (SNb, SName, amail, Gender, ClassYear, GPA)



    delimiter $
    CREATE TRIGGER amail_replace_students AFTER INSERT
    ON Students
    BEGIN
    DECLARE host varchar(10);
    SET host = '(at)usna(dot)edu';
    IFNULL(amail, CONCAT(SNb,host)
    END$
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    There are multiple syntactical and logical problems in your code.

    1) You need to specify the type, whether it is a row trigger or statement trigger. In your case you need a row trigger.

    2) If you want to modify the record that is inserted you must have a before trigger, not an after trigger.

    3) To access the data being inserted you should use the new variable.

    4) I assume that the excerpt

    Code:
    IFNULL(amail, CONCAT(SNb,host)
    is meant to be an assignment.

    The trigger body would rather be like

    Code:
    begin
         if new.amail is null then
            set new.amail = concat(new.snb,'(at)usna(dot)edu');
        end if;
    end
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    The new variable will replace the old Email in the column?
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,378
    Rep Power
    391
    Since the trigger is executed before the actual insert takes place, the value is modified prior to being inserted into the table.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2013
    Posts
    4
    Rep Power
    0
    Originally Posted by swampBoogie
    Since the trigger is executed before the actual insert takes place, the value is modified prior to being inserted into the table.
    Oh yes. Ok, that makes sense. Thank you very much.

IMN logo majestic logo threadwatch logo seochat tools logo