#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102

    Need help in creating a Trigger


    Hello everyone,

    I am trying to write a trigger for use in my MySQL DB 5.0.51a version.

    What I am trying to do is I have a process that runs and updates a table with either updates or new inserts and sometimes a specific column gets populated with "blank spaces". What I need to do is come up with a trigger to fire off after this other process updates or inserts a record and look for 'blank spaces' in that specific column and change that to 'NULL'. Now I am new to writing this so bare with me here:

    So for a trigger there is two files:

    tblname.TRG - for definming the trigger setup

    tblname.TRN - for trigger definition

    So the name I think to use is aiu_empty_to_null

    so Trigger file name = tblname.TRG

    MySQL> create trigger <trigger_name aiu_empty_to_null>
    after insert, update
    on <tbl_name>
    for each row begin <trigger_stmt>
    set NEW.col_name= ?

    Now I guess I need some help in how to write the two files of tblename.TRG and tblname.TRN

    I am unclear how to write the syntax for this, is it something like:

    when ' ' then set NEW.col_name=NULL;

    Actually I am not sure how to use the TRIM function to look for the empty spaces in that column.

    Any help here would be greatful, I am trying to learn as I go here, and am digging in as I can.

    Thanks in advance...
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by ByGoneYrs
    ...I have a process that runs and updates a table with either updates or new inserts and sometimes a specific column gets populated with "blank spaces".
    i would suggest that you will find it more effective to debug this process and fix the problem there, than to come after with a trigger to correct the bad data


    Originally Posted by ByGoneYrs
    ... so bare with me here
    FYI --

    bear with me = have patience
    bare with me = let's get undressed together

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    LMAO...

    Well the process sometimes does not have a "defined" entry for when it is put into place for real...it is a Financial thing, and it is real. It can get fixed by another process that needs it to be NULL instead of blank spaces. Thus someone must locate and alter the spaces entry to NULL for the other process to work for this other process to enter a entry.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by ByGoneYrs
    Well the process sometimes does not have a "defined" entry for when it is put into place for real...it is a Financial thing, and it is real.
    oh, well, that explains it, then

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    So I guess I need to replace blank spaces with nulls

    Any ideas here folks
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,147
    Rep Power
    4274
    Originally Posted by ByGoneYrs
    Any ideas here folks
    i assume by "folks" you mean anyone else besides me?

    because i already told you how to fix it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    I understand what you said, but what your requesting is "beyond" the scope of what I can do/fix or have the scope of doing currently.

    As I said the "Fix " was discovered and that is to manually find each record that has a blank space for a specific column, and manually alter it to a Null. I am trying to do something that would be a Trigger that could find them and fix them so this other process would work.

    Now I am also trying to learn from this as well too. One must understand completely and learn as you go.

    I was just asking for anyone, including you for help in this. Also those with more skill than mine are the ones telling me to pursue this line for the issue. One must do what the big guy said and asks for.

    I should also point out that their is a Business Needs/Reason why everyone once and a while their is a entry in this column that does not have a ID and spaces are put in their. Then they change it to a NULL, and another process is run that changes that NULL and assigns a ID for this unknown one.
  14. #8
  15. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    Digging arround on the web I found the following code...

    As a example:

    if new.'process_id' not in(select 'process id' from 'project_key') then
    if new.'process ID' = ' ' then
    set new.'process ID' = null;
  16. #9
  17. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    Ok to further clarify, the code has been fixed BUT not all of the multiple clients code is schedualed to be upgraded for some time. So creating a Trigger to fire after either a Insert or Update to now 4 columns is needed.

    So this is a interupgrade fix to the client code until a upgrade can be schedualed and their systems taken down to upgrade and etc. The real fix will go in with the next code upgrade, but that needs to be schedualed for each client and etc.

    So first can one trigger cover both Insert and Update and might it look like this to start with...

    As for the trigger name aiu_empty_to_null was what I was thinking, with the a for after and i for insert and u for update, and empty to null being very descriptive of what the trigger was doing. Or should I name it: after_record_b_insert_update. Thus my question can I use one trigger for both updates and inserts or do I need to have two?

    the table name is RECORD B

    DELIMITER$$
    create trigger <trigger_name>
    after INSERT, UPDATE
    on <tbl_name>
    for each row begin

    <syntax to do the find the spaces in the 4 columns and then repalce any they find with a NULL>

    END$$
    DELIMITER ;


    Now this is where I am not sure how to write the rest of this syntax. I am not a Developer but I am trying here.

    set new.col_name =

    I am not sure how to do the set fuction and etc.

    Each of these four columns that are called;

    CAPITAL CURRENCY
    PAYMENT CURRENCY
    PRIMARY CURRENCY
    SECOND CURRENCY

    they are all VARCHAR(3) so they should either be NULL or have a three letter code in each like USD or etc. But some because of the buggy code puts 3 spaces there when each has a default value of NULL. So I need to run this trigger(s) after every update and insert on this table to correct this issue.

    not sure how to write the syntax of how to find the null, might it be this:

    if new.'col_name' =' ' then
    set new.'col_name' = NULL

    As I said I need some help in writing this whole thing and am trying to learn as I go here.
  18. #10
  19. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    UPDATE: the table is called Type1Query
    The column name that is needed to fix is CNTRY_CD a varchar(3)

    Ok I discovered that I can use the same trigger for both INSERT and UPDATE by just using the syntax of:

    DELIMITER$$
    CREATE TRIGGER after_Type1Query_insert_update
    AFTER INSERT,UPDATE
    ON Type1Query
    FOR EACH ROW
    BEGIN


    <sql syntax to find and replace the spaces to null in 1 column>
    IF NEW.CNTRY_CD=' ' then
    SET NEW.CNTRY_CD=NULL

    END$$
    DELIMITER;


    Is this correct?
  20. #11
  21. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    Or would this work for the Syntax statement to locate and replace the 3 empty spaces in this one column to NULL

    UPDATE Type1Query
    SET CNTRY_CD = REPLACE(CNTRY_CD,' ','')

    The goal here is in this one column called CNTRY_CD to find any entry that has empty space and change it to NULL
  22. #12
  23. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    Ok with some more play I discovered that the table name I really needed was RecordD9Major instead of the View of Type1Query

    next I ran the following syntax and updated the table and it worked:

    update RecordD9Major set IssuerIncorpCountry = NULL where LENGTH(TRIM(IssuerIncorpCountry)) =0;

    that worked and updated and got rid of the blank spaces and made them a NULL

    so the new syntax I need for my trigger is:

    DELIMITER$$
    CREATE TRIGGER after_RecordD9Major_insert_update
    AFTER INSERT,UPDATE
    ON IssuerIncorpCountry
    FOR EACH ROW
    BEGIN
    IF LENGTH(TRIM(NEW.IssuerIncorpCountry))= 0 THEN
    SET NEW.IssuerIncorpCountry = NULL;
    END$$
    DELIMITER;

    Is this the way it should read? Is all my syntax correct?
  24. #13
  25. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Location
    Southern New Jersey, USA
    Posts
    205
    Rep Power
    102
    My question now is my above syntax correct for a Trigger, I have tested the code and it does fix the problem...replacing <blank space> with a <NULL>.

    My next question is how to set up the trigger in procedure of .TRG and .TRN files. I am a little confused by this step.

IMN logo majestic logo threadwatch logo seochat tools logo