Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2011
    Posts
    10

    Unanswered: Trigger w/ Concat_WS

    Ok so just a small bit of background in what I am trying to accomplish. I have a mysql db which stores asset information. I am trying to incorporate a logging feature which will allow users to see who modified the record last and what the OLD.Field(s) were and what the NEW.Field(s) are. In so doing, I have created a trigger which posts data to a table before an update has processed. Two of the tuples are populated with a concat_ws command as I simply want to bunch the old modified row data into a single field as well as the new row data into its own field. See below for my code. Please forgive the abundance of formatting as I am trying to simplify any application programming.

    So here is my problem: the below code will not process the NEW.Field Data within a concat_ws selection. What other option is open to doing exactly what the trigger below should be capable of? Or is my syntax simply off?

    -------------------------------------------------------
    DELIMITER $$

    CREATE TRIGGER ON_UPDATE_LOG_CHANGES
    BEFORE UPDATE ON asset
    FOR EACH ROW
    BEGIN
    IF OLD.staff_name != NEW.staff_name
    THEN
    INSERT INTO `soimsdb`.`soimsdb_logs`
    (
    log_table ,
    log_row ,
    log_query ,
    log_old_staff ,
    log_new_staff ,
    log_old_data ,
    log_new_data ,
    log_timestamp

    )
    VALUES
    (
    'View Modification',
    OLD.asset_id,
    'Modification was completed as an \'UPDATE\'',
    (SELECT `s_usern` FROM `soimsdb`.`staff` WHERE OLD.staff_name = s_usern),
    (SELECT `s_usern` FROM `soimsdb`.`staff` WHERE NEW.staff_name = s_usern),
    (SELECT CONCAT_WS('','<center>','<table width="190" align="top" border="2" cellspacing="1" cellpadding="1" bgcolor="#93c582">',
    '<tr>','<td>','SO Serial','</td>','<td>',OLD.so_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Serial','</td>','<td>',OLD.a_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Name','</td>','<td>',OLD.a_name,'</td>','</tr>',
    '<tr>','<td>','Asset IP #1','</td>','<td>',OLD.a_ip_1,'</td>','</tr>',
    '<tr>','<td>','Asset IP #2','</td>','<td>',OLD.a_ip_2,'</td>','</tr>',
    '<tr>','<td>','Asset IP #3','</td>','<td>',OLD.a_ip_3,'</td>','</tr>',
    '<tr>','<td>','Asset IP #4','</td>','<td>',OLD.a_ip_4,'</td>','</tr>',
    '<tr>','<td>','Verizon IP','</td>','<td>',OLD.a_ip_5,'</td>','</tr>',
    '<tr>','<td>','Asset Notes','</td>','<td>',OLD.a_notes,'</td>','</tr>',
    '<tr>','<td>','Asset Aquired','</td>','<td>',OLD.a_aquired,'</td>','</tr>',
    '<tr>','<td>','Telephone','</td>','<td>',OLD.mtn,'</td>','</tr>',
    '<tr>','<td>','Asset Modify Date','</td>','<td>',OLD.a_modate,'</td>','</tr>',
    '<tr>','<td>','Warranty Period','</td>','<td>',OLD.a_war_period,'</td>','</tr>',
    '<tr>','<td>','Expiration Date','</td>','<td>',OLD.a_expire,'</td>','</tr>',
    '<tr>','<td>','OS','</td>','<td>',OLD.a_os,'</td>','</tr>',
    '<tr>','<td>','In Service','</td>','<td>',OLD.in_service,'</td>','</tr>',
    '<tr>','<td>','OS Key','</td>','<td>',OLD.os_key,'</td>','</tr>',
    '<tr>','<td>','Net Motion','</td>','<td>',OLD.netmotion,'</td>','</tr>',
    '<tr>','<td>','PPT','</td>','<td>',OLD.ppt,'</td>','</tr>',
    '<tr>','<td>','MAC Address','</td>','<td>',OLD.mac_addy,'</td>','</tr>',
    '<tr>','<td>','Asset Config','</td>','<td>',OLD.a_config,'</td>','</tr>',
    '<tr>','<td>','Username','</td>','<td>',OLD.USER_user_id,'</td>','</tr>',
    '<tr>','<td>','First Name','</td>','<td>',OLD.personnel_user_fname,'</td>','</tr>',
    '<tr>','<td>','Last Name','</td>','<td>',OLD.personnel_user_lname,'</td>','</tr>',
    '<tr>','<td>','Email Address','</td>','<td>',OLD.personnel_user_email,'</td>','</tr>',
    '<tr>','<td>','Division','</td>','<td>',OLD.personnel_user_div,'</td>','</tr>',
    '<tr>','<td>','Manufacturer','</td>','<td>',OLD.manufacturers_m_id,'</td>','</tr>',
    '<tr>','<td>','MNE','</td>','<td>',OLD.mne_station_mne_sta_id,'</td>','</tr>',
    '<tr>','<td>','Product Type','</td>','<td>',OLD.product_type_p_id,'</td>','</tr>',
    '<tr>','<td>','Product Model','</td>','<td>',OLD.product_model_pro_model_id,'</td>','</tr>',
    '<tr>','<td>','Old Staff Name','</td>','<td>',OLD.staff_name,'</td>','</tr>','</table>','</center>')
    FROM `asset` WHERE OLD.asset_id = `asset_id`),
    (SELECT CONCAT_WS('','<center>','<table width="190" align="top" border="2" cellspacing="1" cellpadding="1" bgcolor="#638ca9">',
    '<tr>','<td>','SO Serial','</td>','<td>',NEW.so_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Serial','</td>','<td>',NEW.a_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Name','</td>','<td>',NEW.a_name,'</td>','</tr>',
    '<tr>','<td>','Asset IP #1','</td>','<td>',NEW.a_ip_1,'</td>','</tr>',
    '<tr>','<td>','Asset IP #2','</td>','<td>',NEW.a_ip_2,'</td>','</tr>',
    '<tr>','<td>','Asset IP #3','</td>','<td>',NEW.a_ip_3,'</td>','</tr>',
    '<tr>','<td>','Asset IP #4','</td>','<td>',NEW.a_ip_4,'</td>','</tr>',
    '<tr>','<td>','Verizon IP','</td>','<td>',NEW.a_ip_5,'</td>','</tr>',
    '<tr>','<td>','Asset Notes','</td>','<td>',NEW.a_notes,'</td>','</tr>',
    '<tr>','<td>','Asset Aquired','</td>','<td>',NEW.a_aquired,'</td>','</tr>',
    '<tr>','<td>','Telephone','</td>','<td>',NEW.mtn,'</td>','</tr>',
    '<tr>','<td>','Asset Modify Date','</td>','<td>',NEW.a_modate,'</td>','</tr>',
    '<tr>','<td>','Warranty Period','</td>','<td>',NEW.a_war_period,'</td>','</tr>',
    '<tr>','<td>','Expiration Date','</td>','<td>',NEW.a_expire,'</td>','</tr>',
    '<tr>','<td>','OS','</td>','<td>',NEW.a_os,'</td>','</tr>',
    '<tr>','<td>','In Service','</td>','<td>',NEW.in_service,'</td>','</tr>',
    '<tr>','<td>','OS Key','</td>','<td>',NEW.os_key,'</td>','</tr>',
    '<tr>','<td>','Net Motion','</td>','<td>',NEW.netmotion,'</td>','</tr>',
    '<tr>','<td>','PPT','</td>','<td>',NEW.ppt,'</td>','</tr>',
    '<tr>','<td>','MAC Address','</td>','<td>',NEW.mac_addy,'</td>','</tr>',
    '<tr>','<td>','Asset Config','</td>','<td>',NEW.a_config,'</td>','</tr>',
    '<tr>','<td>','Username','</td>','<td>',NEW.USER_user_id,'</td>','</tr>',
    '<tr>','<td>','First Name','</td>','<td>',NEW.personnel_user_fname,'</td>','</tr>',
    '<tr>','<td>','Last Name','</td>','<td>',NEW.personnel_user_lname,'</td>','</tr>',
    '<tr>','<td>','Email Address','</td>','<td>',NEW.personnel_user_email,'</td>','</tr>',
    '<tr>','<td>','Division','</td>','<td>',NEW.personnel_user_div,'</td>','</tr>',
    '<tr>','<td>','Manufacturer','</td>','<td>',NEW.manufacturers_m_id,'</td>','</tr>',
    '<tr>','<td>','MNE','</td>','<td>',NEW.mne_station_mne_sta_id,'</td>','</tr>',
    '<tr>','<td>','Product Type','</td>','<td>',NEW.product_type_p_id,'</td>','</tr>',
    '<tr>','<td>','Product Model','</td>','<td>',NEW.product_model_pro_model_id,'</td>','</tr>',
    '<tr>','<td>','Old Staff Name','</td>','<td>',NEW.staff_name,'</td>','</tr>','</table>','</center>')
    FROM `asset` WHERE NEW.asset_id = `asset_id`),
    NOW());
    END IF;

    END;

    -----------------------------------------

    thanks,

    Ryan

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Ryan,

    I have looked at your code and there is a lot of redundancy here. You are creating a trigger on update of the asset table. However, you then attempt to access the same record you are updating to get old values and new values. This is not needed.

    I have not tested this but if this should give you a general idea of how to do this more efficiently. Also as you are simply concatenating the information just use CONCAT.

    DELIMITER $$

    CREATE TRIGGER ON_UPDATE_LOG_CHANGES
    BEFORE UPDATE ON asset
    FOR EACH ROW
    BEGIN
    IF OLD.staff_name != NEW.staff_name
    THEN
    INSERT INTO `soimsdb`.`soimsdb_logs`
    (
    log_table ,
    log_row ,
    log_query ,
    log_old_staff ,
    log_new_staff ,
    log_old_data ,
    log_new_data ,
    log_timestamp

    )
    VALUES
    (
    'View Modification',
    OLD.asset_id,
    'Modification was completed as an \'UPDATE\'',
    (SELECT `s_usern` FROM `soimsdb`.`staff` WHERE OLD.staff_name = s_usern),
    (SELECT `s_usern` FROM `soimsdb`.`staff` WHERE NEW.staff_name = s_usern),
    CONCAT('<center>','<table width="190" align="top" border="2" cellspacing="1" cellpadding="1" bgcolor="#93c582">',
    '<tr>','<td>','SO Serial','</td>','<td>',OLD.so_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Serial','</td>','<td>',OLD.a_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Name','</td>','<td>',OLD.a_name,'</td>','</tr>',
    '<tr>','<td>','Asset IP #1','</td>','<td>',OLD.a_ip_1,'</td>','</tr>',
    '<tr>','<td>','Asset IP #2','</td>','<td>',OLD.a_ip_2,'</td>','</tr>',
    '<tr>','<td>','Asset IP #3','</td>','<td>',OLD.a_ip_3,'</td>','</tr>',
    '<tr>','<td>','Asset IP #4','</td>','<td>',OLD.a_ip_4,'</td>','</tr>',
    '<tr>','<td>','Verizon IP','</td>','<td>',OLD.a_ip_5,'</td>','</tr>',
    '<tr>','<td>','Asset Notes','</td>','<td>',OLD.a_notes,'</td>','</tr>',
    '<tr>','<td>','Asset Aquired','</td>','<td>',OLD.a_aquired,'</td>','</tr>',
    '<tr>','<td>','Telephone','</td>','<td>',OLD.mtn,'</td>','</tr>',
    '<tr>','<td>','Asset Modify Date','</td>','<td>',OLD.a_modate,'</td>','</tr>',
    '<tr>','<td>','Warranty Period','</td>','<td>',OLD.a_war_period,'</td>','</tr>',
    '<tr>','<td>','Expiration Date','</td>','<td>',OLD.a_expire,'</td>','</tr>',
    '<tr>','<td>','OS','</td>','<td>',OLD.a_os,'</td>','</tr>',
    '<tr>','<td>','In Service','</td>','<td>',OLD.in_service,'</td>','</tr>',
    '<tr>','<td>','OS Key','</td>','<td>',OLD.os_key,'</td>','</tr>',
    '<tr>','<td>','Net Motion','</td>','<td>',OLD.netmotion,'</td>','</tr>',
    '<tr>','<td>','PPT','</td>','<td>',OLD.ppt,'</td>','</tr>',
    '<tr>','<td>','MAC Address','</td>','<td>',OLD.mac_addy,'</td>','</tr>',
    '<tr>','<td>','Asset Config','</td>','<td>',OLD.a_config,'</td>','</tr>',
    '<tr>','<td>','Username','</td>','<td>',OLD.USER_user_id,'</td>','</tr>',
    '<tr>','<td>','First Name','</td>','<td>',OLD.personnel_user_fname,'</td>','</tr>',
    '<tr>','<td>','Last Name','</td>','<td>',OLD.personnel_user_lname,'</td>','</tr>',
    '<tr>','<td>','Email Address','</td>','<td>',OLD.personnel_user_email,'</td>','</tr>',
    '<tr>','<td>','Division','</td>','<td>',OLD.personnel_user_div,'</td>','</tr>',
    '<tr>','<td>','Manufacturer','</td>','<td>',OLD.manufacturers_m_id,'</td>','</tr>',
    '<tr>','<td>','MNE','</td>','<td>',OLD.mne_station_mne_sta_id,'</td>','</tr>',
    '<tr>','<td>','Product Type','</td>','<td>',OLD.product_type_p_id,'</td>','</tr>',
    '<tr>','<td>','Product Model','</td>','<td>',OLD.product_model_pro_model_id,'</td>','</tr>',
    '<tr>','<td>','Old Staff Name','</td>','<td>',OLD.staff_name,'</td>','</tr>','</table>','</center>'),
    CONCAT('<center>','<table width="190" align="top" border="2" cellspacing="1" cellpadding="1" bgcolor="#638ca9">',
    '<tr>','<td>','SO Serial','</td>','<td>',NEW.so_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Serial','</td>','<td>',NEW.a_serial,'</td>','</tr>',
    '<tr>','<td>','Asset Name','</td>','<td>',NEW.a_name,'</td>','</tr>',
    '<tr>','<td>','Asset IP #1','</td>','<td>',NEW.a_ip_1,'</td>','</tr>',
    '<tr>','<td>','Asset IP #2','</td>','<td>',NEW.a_ip_2,'</td>','</tr>',
    '<tr>','<td>','Asset IP #3','</td>','<td>',NEW.a_ip_3,'</td>','</tr>',
    '<tr>','<td>','Asset IP #4','</td>','<td>',NEW.a_ip_4,'</td>','</tr>',
    '<tr>','<td>','Verizon IP','</td>','<td>',NEW.a_ip_5,'</td>','</tr>',
    '<tr>','<td>','Asset Notes','</td>','<td>',NEW.a_notes,'</td>','</tr>',
    '<tr>','<td>','Asset Aquired','</td>','<td>',NEW.a_aquired,'</td>','</tr>',
    '<tr>','<td>','Telephone','</td>','<td>',NEW.mtn,'</td>','</tr>',
    '<tr>','<td>','Asset Modify Date','</td>','<td>',NEW.a_modate,'</td>','</tr>',
    '<tr>','<td>','Warranty Period','</td>','<td>',NEW.a_war_period,'</td>','</tr>',
    '<tr>','<td>','Expiration Date','</td>','<td>',NEW.a_expire,'</td>','</tr>',
    '<tr>','<td>','OS','</td>','<td>',NEW.a_os,'</td>','</tr>',
    '<tr>','<td>','In Service','</td>','<td>',NEW.in_service,'</td>','</tr>',
    '<tr>','<td>','OS Key','</td>','<td>',NEW.os_key,'</td>','</tr>',
    '<tr>','<td>','Net Motion','</td>','<td>',NEW.netmotion,'</td>','</tr>',
    '<tr>','<td>','PPT','</td>','<td>',NEW.ppt,'</td>','</tr>',
    '<tr>','<td>','MAC Address','</td>','<td>',NEW.mac_addy,'</td>','</tr>',
    '<tr>','<td>','Asset Config','</td>','<td>',NEW.a_config,'</td>','</tr>',
    '<tr>','<td>','Username','</td>','<td>',NEW.USER_user_id,'</td>','</tr>',
    '<tr>','<td>','First Name','</td>','<td>',NEW.personnel_user_fname,'</td>','</tr>',
    '<tr>','<td>','Last Name','</td>','<td>',NEW.personnel_user_lname,'</td>','</tr>',
    '<tr>','<td>','Email Address','</td>','<td>',NEW.personnel_user_email,'</td>','</tr>',
    '<tr>','<td>','Division','</td>','<td>',NEW.personnel_user_div,'</td>','</tr>',
    '<tr>','<td>','Manufacturer','</td>','<td>',NEW.manufacturers_m_id,'</td>','</tr>',
    '<tr>','<td>','MNE','</td>','<td>',NEW.mne_station_mne_sta_id,'</td>','</tr>',
    '<tr>','<td>','Product Type','</td>','<td>',NEW.product_type_p_id,'</td>','</tr>',
    '<tr>','<td>','Product Model','</td>','<td>',NEW.product_model_pro_model_id,'</td>','</tr>',
    '<tr>','<td>','Old Staff Name','</td>','<td>',NEW.staff_name,'</td>','</tr>','</table>','</center>'),
    NOW());
    END IF;

    END;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jul 2011
    Posts
    10
    First thank you for the response. I have a question reference the use of simply CONCAT()? If my row contains any NULL values my return appears empty. In reading the CONCAT_WS() it appears to address the NULL values but requires a string seperator. I know you could code IF NULL statements but I had chose to take advantage of the CONCAT_WS() instead. Is this not true or am I missing something?

    EDIT: Ok I tried the supplied code and using the CONCAT() shows an empty field. Also I am still facing the problem pertaining to the NEW.Field(s). Its not populating the new data after the UPDATE has applied. So OLD.a_asset = 1 BEFORE UPDATE & NEW.a_asset = 1 AFTER UPDATE. The data is changing properly its simply this TRIGGER isnt capture the before and after UPDATE like it should and I think its associated to the CONCAT() usage.

    thanks,

    Ryan
    Last edited by rpot; 07-12-11 at 20:40.

  4. #4
    Join Date
    Mar 2006
    Posts
    56
    Quote Originally Posted by rpot View Post
    First thank you for the response. I have a question reference the use of simply CONCAT()? If my row contains any NULL values my return appears empty. In reading the CONCAT_WS() it appears to address the NULL values but requires a string seperator. I know you could code IF NULL statements but I had chose to take advantage of the CONCAT_WS() instead. Is this not true or am I missing something?
    You need IFNULL function even when you use CONCAT_WS.

    Refer to the second example of CONCAT_WS use in The Manual. The function skips the null value, thus there is only one comma between First name and Last Name.

    I don't know if it affects the performance but, strictly speaking for saving clutter from codes, I think you should create a user defined function that works like below:
    Code:
    SELECT
    CONCAT('<tr>'
         , CONCAT('<td>'
                , CONCAT_WS('</td><td>'
                          , IFNULL('SO Serial','')
                          , IFNULL('OLD.so_serial','')
                          , IFNULL(NULL,'')
                  )
                , '</td>'
           )
         , '</tr>') asdf;
    And you would write something like this:
    SELECT CONCAT('<center>, <table>'
    , HTML_TABLE_ROW('SO Serial',OLD.so_serial)
    , HTML_TABLE_ROW(. . . . .)
    , . . . . .
    , '</table>, </center>');
    Last edited by Ikviens; 07-13-11 at 00:12.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ikviens View Post
    You need IFNULL function even when you use CONCAT_WS.
    actually, no, you don't

    the manual says "CONCAT_WS() does not skip empty strings. However, it does skip any NULL values"

    so you don't need to test for null with IFNULL and substitute an empty string -- that's not necessary
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2006
    Posts
    56
    Yes, you are right, Rudy. Sorry for the confusion.

  7. #7
    Join Date
    Jul 2011
    Posts
    10
    So the question still stands how do I retrieve NEW.Field data from a trigger when using the CONCAT_WS() operator? Are these two functions capable of working in conjunction of each other? I have searched everywhere looking for an example of their combined usage but havent been to successful.

    My understanding of "BEFORE UPDATE ON" within a trigger I should be able to read the rows data before and after completion, correct?

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I have created a mock up of a users audit table as follows:

    truncate table users_audit;
    select * from users;

    drop trigger user_updates;
    delimiter $$
    create trigger user_updates
    before update on users
    for each row
    begin
    if old.username <> new.username then
    insert into users_audit(uid, olduser, newuser) values (old.uid, concat(old.username,",",new.username), new.username);
    end if;
    end;
    $$

    delimiter ;
    update users set username = 'Ronnie' where username = 'Ron';
    select * from users_audit;

    and when this is run I get as is expected:

    mysql> source trig.sql
    Query OK, 0 rows affected (0.00 sec)

    +-----+----------+
    | uid | username |
    +-----+----------+
    | 1 | Tom |
    | 2 | Dick |
    | 3 | Harry |
    | 4 | Jerry |
    | 5 | Frank |
    | 6 | Ron |
    | 7 | Bud |
    | 8 | NULL |
    | 9 | Tom |
    | 10 | Dick |
    | 11 | Harry |
    | 12 | Jerry |
    | 13 | Frank |
    | 14 | Ron |
    | 15 | Bud |
    | 16 | NULL |
    +-----+----------+
    16 rows in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)

    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2 Changed: 2 Warnings: 0

    +------+------------+---------+
    | uid | olduser | newuser |
    +------+------------+---------+
    | 6 | Ron,Ronnie | Ronnie |
    | 14 | Ron,Ronnie | Ronnie |
    +------+------------+---------+
    2 rows in set (0.00

    I also changed the concat with concat_ws and this still worked. This was without testing a null value. However, when testing against a null value this failed.

    mysql> select * from users;

    +-----+----------+
    | uid | username |
    +-----+----------+
    | 1 | Tom |
    | 2 | Dick |
    | 3 | Harry |
    | 4 | Jerry |
    | 5 | Frank |
    | 6 | Ron |
    | 7 | Bud |
    | 8 | NULL |
    | 9 | Tom |
    | 10 | Dick |
    | 11 | Harry |
    | 12 | Jerry |
    | 13 | Frank |
    | 14 | Ron |
    | 15 | Bud |
    | 16 | NULL |
    +-----+----------+
    16 rows in set (0.00 sec)

    mysql> update users set username = 'abc' where uid = 16;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> select * from users_audit;
    +------+------------+---------+
    | uid | olduser | newuser |
    +------+------------+---------+
    | 6 | Ron,Ronnie | Ronnie |
    | 14 | Ron,Ronnie | Ronnie |
    +------+------------+---------+
    2 rows in set (0.00 sec)


    So to answer your question, CONCAT and CONCAT_WS do work as long as you are not including a null value.
    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
  •