2

Some 2-3 years I had written some triggers (one per table) in SQL server allowing me to log queries in our main software.

ALTER TRIGGER [dbo].[SUIVI_REQUETE_CAT_CATBRUT] ON [dbo].[CAT_CATBRUT] AFTER INSERT, DELETE, UPDATE

AS
DECLARE
  @date as nvarchar(10),
  @heure as nvarchar(15),
  @nomUtilisateur as nvarchar(50),
  @poste as nvarchar(50),
  @requete as nvarchar(MAX)
BEGIN
  set @requete = 'DBCC INPUTBUFFER(' + str(@@SPID) + ')'

  CREATE TABLE temporaire (EventType nvarchar(30), Parameters int, EventInfo nvarchar(MAX))
  insert into temporaire exec(@requete)

  set @requete = (SELECT EventInfo FROM temporaire)
  drop table temporaire

  set @poste = (SELECT HOST_NAME() AS HostName)
  set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)
  set @date =(SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD])
  set @heure =(select CONVERT(VARCHAR,getdate(),108))

  INSERT INTO INF_LOG_REQUETE (REQUETE, POSTE, NOM_UTILISATEUR, [DATE], HEURE) VALUES (LEFT(@requete, 4000), @poste, @nomUtilisateur, @date, @heure)
End

I'm now at the head of a new web division and one of our goal is allow my web-software to share data with our main software. I would like to save some key rows in the tables (customer, vehicle, employee, calendar, etc.) we want to share between both softwares.

I have three questions regarding my problem:

  1. I would like to log the query only for a specific database user (we do not want to get stuck in an infinite loop of sending back and forth the same modification). In SQL server I used that to detect the user:

    set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)

Is it possible to detect the user that executed the query in MySQL?

  1. In SQL server, I was able to use the same trigger on insert, update and delete actions.

Is it possible to do the same in MySQL or do I have to write three different trigger from every table?

  1. If the same trigger can be used for multiple action (insert, update, delete), is it possible to detect which action (insert, update, delete) launched the trigger?

Additional note: I want to record only specific fields (containing the key data to share) in the table. I really want to write a trigger for every table so a PHP function can automatically parse the field with data and build an XML file. By example, I want a field in my log table containing:

{table}:[vehicle], {action}:[insert], {field}:[make], {value}:[acura], {field}:[model], {value}:[S7], ...

And produce XML like the following:

<LIST>
    <QUERY>
        <TABLE>vehicle</TABLE>
        <ACTION>insert<ACTION>
        <FIELD>make</FIELD>
        <VALUE>acura</VALUE>
        <FIELD>model</FIELD>
        <VALUE>S7</VALUE>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
</LIST>

Thank you

  • That is not MySQL syntax. Please change the tag and the title to reflect the correct database software. – Rick James Jan 18 '16 at 18:52
  • That's the trigger I had written in SQL server. From this, my question (in three points) is about the difference between MySQL and SQL Server (since every database engine implements the SQL standard a different way). – Jonathan Parent Lévesque Jan 18 '16 at 19:15
0

Ok, after some research, here's the answers I found.

Q1: Is it possible to detect the user that executed the query in MySQL?

It is possible to detect with which user by using: select user();

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_user

Beware: In triggers and events current_user() returns the user who defined the object (in SP and views unless if defined with the SQL SECURITY INVOKER). You really need to use the user() function to return the invoker in those contexts.

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_current-user

Q2: Is it possible to fire more than one action (INSERT, UPDATE, DELETE) on the same trigger?

Most unfortunately, at the current time (2016/02/01), it is not possible to fire two actions on the same time in MySQL. The best we can do is to regroup the common coding within a stored procedure.

The following procedure is one possible way to work around this problem. It expects in input:

  1. callingTable: The name of the table for which we want to record the query (must be in the white list).
  2. action: Either 'DELETE', 'INSERT' or 'UPDATE'.
  3. fieldsValues: a string containing a valid pair of field/value in XML. By example: <field>ID</field><value>1616</value>

    CREATE PROCEDURE `dataLinkRecorder` (
    IN callingTable VARCHAR (25),
    IN action ENUM('DELETE', 'INSERT', 'UPDATE'),
    IN fieldsValues TEXT
    )
    BEGIN
    DECLARE callingUser VARCHAR(25) DEFAULT '';
    DECLARE fieldCount SMALLINT UNSIGNED DEFAULT 0;
    DECLARE fieldCCount SMALLINT UNSIGNED DEFAULT 0;
    DECLARE valueCount SMALLINT UNSIGNED DEFAULT 0;
    DECLARE valueCCount SMALLINT UNSIGNED DEFAULT 0;
    
    IF (TRIM(fieldsValues) <> '') THEN
    
        /* the calling table must be one in the white list */
        SELECT CASE 
            WHEN UPPER(callingTable) IN (
                'CATEGORY',
                'CUSTOMER',
                'MAKE',
                'MODEL',
                'PRODUCT',
                'VEHICLE'
            )
            THEN TRIM(UPPER(callingTable))
            ELSE '' 
            END AS myTable
            INTO callingTable;
    
         SELECT TRIM(
         UPPER(
            IFNULL(USER(), 'CLIENT')
         )
         ) AS myUser
    INTO callingUser;
    
    /* do not record modifications from tier software */
    IF (callingTable <> '' AND INSTR(callingUser, 'CLIENT') < 1) THEN
    
        SELECT IFNULL(
            ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<field>', ''))) / 7)
        , 0) AS fCount,
        IFNULL(
            ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</field>', ''))) / 8)
        , 0) AS fCCount,
        IFNULL(
            ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '<value>', ''))) / 7)
        , 0) AS vCount,
        IFNULL(
            ((CHAR_LENGTH(fieldsValues) - CHAR_LENGTH(REPLACE(fieldsValues, '</value>', ''))) / 8)
        , 0) AS vCCount
        INTO fieldCount, fieldCCount, valueCount, valueCCount;
    
        /* validate the field/value XML pair */
        IF (fieldCount > 0 AND
            valueCount > 0 AND
            fieldCount = fieldCCount AND
            valueCount = valueCCount AND
            fieldCount = valueCCount
        ) THEN
    
            INSERT INTO `pc_datalink` 
                (`table_name`, `query_xml`) 
            VALUES(
                callingTable,
                CONCAT(
                    '<query>',
                        '<table>', callingTable, '</table>',
                        '<action>', action, '</action>',
                        fieldsValues,
                    '</query>'
                )
            );
        END IF;
    END IF;
    END IF;
    END //
    

Will insert values in the following table:

CREATE TABLE `pc_datalink` (
    `id` int(10) UNSIGNED NOT NULL,
    `table_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
    `query_xml` text COLLATE utf8_unicode_ci NOT NULL,
    `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `AK_DATE_ADDED` (`date_added`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

By example, a very simple trigger like this one:

CREATE TRIGGER `dataLinkDeleteVehicle` AFTER DELETE ON `pc_vehicle`
FOR EACH ROW
BEGIN
IF (OLD.original_id <> '') THEN

    CALL dataLinkRecorder(
        'VEHICLE',
        'DELETE',
        CONCAT(
            '<field>ID</field><value>',
            OLD.original_id,
            '</value>'
        )
    );
END IF; 
END //

Will generate a line in the table like this one:

enter image description here

Don't hesitate to ask for details if you still have questions,

Good success everyone

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.