{"id":636,"date":"2025-07-28T18:07:20","date_gmt":"2025-07-28T18:07:20","guid":{"rendered":"https:\/\/mouryasolutions.in\/blog\/?p=636"},"modified":"2025-07-28T18:08:20","modified_gmt":"2025-07-28T18:08:20","slug":"how-to-create-a-dynamic-logging-function-inside-a-stored-procedure","status":"publish","type":"post","link":"https:\/\/mouryasolutions.in\/blog\/how-to-create-a-dynamic-logging-function-inside-a-stored-procedure\/","title":{"rendered":"How to Create a Dynamic Logging Function Inside a Stored Procedure"},"content":{"rendered":"<div class=\"post-content\">\n<blockquote class=\"wp-block-quote\">\n<p><strong>Goal:<\/strong> Dynamically log the <em>before-update<\/em> state of any row in any table, as JSON, inside your <code>Store_Log<\/code> table using stored procedures and dynamic SQL in <strong>SQL<\/strong>.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\ud83e\udde0 Why This Matters<\/h2>\n\n\n\n<p>Sometimes you need a way to <strong>track changes<\/strong> to critical data \u2014 who changed what, and what the data looked like <em>before<\/em> the change.<\/p>\n\n\n\n<p>Instead of hard-coding every log, let\u2019s build a <strong>universal, reusable<\/strong> solution using dynamic SQL that:<\/p>\n\n\n\n<ul>\n<li>Works on <strong>any table<\/strong><\/li>\n\n\n\n<li>Logs data <strong>before updates<\/strong><\/li>\n\n\n\n<li>Stores logs as <strong>JSON<\/strong><\/li>\n\n\n\n<li>Is triggered <strong>inside a stored procedure<\/strong><\/li>\n<\/ul>\n\n\n\n<p>This is a <strong>minimal dynamic audit log<\/strong> \u2014 no triggers, no plugins, just native SQL.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\u2705 What You\u2019ll Build<\/h2>\n\n\n\n<p>A dynamic logging solution that does the following:<\/p>\n\n\n\n<ol>\n<li><strong>Fetches the row<\/strong> before it&#8217;s updated \u2014 dynamically<\/li>\n\n\n\n<li><strong>Converts the row to JSON<\/strong><\/li>\n\n\n\n<li><strong>Stores that JSON<\/strong> in a <code>Store_Log<\/code> table<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\ud83d\udd27 Step-by-Step Guide<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\u2705 Step 1: Create a Helper Procedure \u2013 Get a Row as JSON<\/h3>\n\n\n\n<p>We start by creating a stored procedure that:<\/p>\n\n\n\n<ul>\n<li>Accepts a table name, primary key column, and primary key value<\/li>\n\n\n\n<li>Uses <code>INFORMATION_SCHEMA.COLUMNS<\/code> to get all column names<\/li>\n\n\n\n<li>Dynamically builds a <code>SELECT JSON_OBJECT(...)<\/code> query<\/li>\n\n\n\n<li>Returns the JSON object<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>\n\nCREATE PROCEDURE GetRowAsJson (\n    IN in_table_name VARCHAR(64),\n    IN in_primary_key_name VARCHAR(64),\n    IN in_id_value VARCHAR(64),\n    OUT out_json_result JSON\n)\nBEGIN\n    DECLARE col_list TEXT DEFAULT '';\n    DECLARE finished INT DEFAULT 0;\n    DECLARE col_name VARCHAR(64);\n\n    DECLARE col_cursor CURSOR FOR\n        SELECT COLUMN_NAME\n        FROM INFORMATION_SCHEMA.COLUMNS\n        WHERE TABLE_NAME = in_table_name\n          AND TABLE_SCHEMA = DATABASE();\n\n    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;\n\n    OPEN col_cursor;\n\n    read_loop: LOOP\n        FETCH col_cursor INTO col_name;\n        IF finished = 1 THEN\n            LEAVE read_loop;\n        END IF;\n        SET col_list = CONCAT_WS(', ', col_list, QUOTE(col_name), ', ', col_name);\n    END LOOP;\n\n    CLOSE col_cursor;\n\n    SET @dyn_sql = CONCAT(\n        'SELECT JSON_OBJECT(', col_list, ') AS json_row ',\n        'FROM ', in_table_name, ' WHERE ', in_primary_key_name, ' = ? LIMIT 1'\n    );\n\n    PREPARE stmt FROM @dyn_sql;\n    SET @id_param = in_id_value;\n    EXECUTE stmt USING @id_param;\n    DEALLOCATE PREPARE stmt;\nEND<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3>\u2705 Step 2: Use It Inside an Update Procedure<\/h3>\n\n\n\n<p>Now let\u2019s say you have an <code>Orders<\/code> table, and you want to update an order&#8217;s total \u2014 but <strong>also log what the row looked like before the update<\/strong>.<\/p>\n\n\n\n<p>Here\u2019s how:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\n\nCREATE PROCEDURE UpdateOrderDynamicLog (\n    IN in_OrderID INT,\n    IN in_NewTotal DECIMAL(10,2),\n    IN in_User VARCHAR(100)\n)\nBEGIN\n    DECLARE json_before JSON;\n\n    -- Step 1: Get the current row as JSON\n    CALL GetRowAsJson('Orders', 'OrderID', in_OrderID, json_before);\n\n    -- Step 2: Perform the update\n    UPDATE Orders\n    SET Total = in_NewTotal\n    WHERE OrderID = in_OrderID;\n\n    -- Step 3: Insert into Store_Log\n    INSERT INTO Store_Log (\n        StoreTableName,\n        StoreTableID,\n        MainTableContent,\n        CreatedBy\n    ) VALUES (\n        'Orders',\n        in_OrderID,\n        json_before,\n        in_User\n    );\nEND<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\ud83e\uddea Test It<\/h2>\n\n\n\n<p>Try running this update and see the log appear:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CALL UpdateOrderDynamicLog(104, 888.88, 'admin_user');\n<\/code><\/pre>\n\n\n\n<p>Now check your <code>Store_Log<\/code> table \u2014 you should see something like this stored:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>{\n  \"OrderID\": 104,\n  \"CustomerName\": \"Alice\",\n  \"Total\": 100.50\n}\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\u2705 Summary<\/h2>\n\n\n\n<figure class=\"wp-block-table\"><table><thead><tr><th>Feature<\/th><th>Implementation<\/th><\/tr><\/thead><tbody><tr><td>Dynamic SELECT of row<\/td><td><code>INFORMATION_SCHEMA.COLUMNS<\/code> + cursor<\/td><\/tr><tr><td>Convert to JSON<\/td><td><code>JSON_OBJECT(col1, val1, ...)<\/code><\/td><\/tr><tr><td>Store in log<\/td><td>INSERT into <code>Store_Log<\/code><\/td><\/tr><tr><td>Works for any table?<\/td><td>\u2705 Yes, fully dynamic<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2>\ud83d\udca1 Want to Take It Further?<\/h2>\n\n\n\n<p>Here are some powerful ideas to extend this approach:<\/p>\n\n\n\n<ul>\n<li><strong>Log After-Update values<\/strong> for comparison<\/li>\n\n\n\n<li>Support <strong>INSERT<\/strong> and <strong>DELETE<\/strong> operations<\/li>\n\n\n\n<li>Add <strong>diff logs<\/strong> to show what changed<\/li>\n\n\n\n<li>Log the <strong>timestamp, IP, or session info<\/strong><\/li>\n\n\n\n<li>Add a <code>ChangeType<\/code> column: <code>'UPDATE'<\/code>, <code>'INSERT'<\/code>, <code>'DELETE'<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n<\/div><!-- AddThis Advanced Settings generic via filter on the_content --><!-- AddThis Share Buttons generic via filter on the_content -->","protected":false},"excerpt":{"rendered":"<p>Goal: Dynamically log the before-update state of any row in<a href=\"https:\/\/mouryasolutions.in\/blog\/how-to-create-a-dynamic-logging-function-inside-a-stored-procedure\/\">Read More<i class=\"fa fa-long-arrow-right\" aria-hidden=\"true\"><\/i><\/a><!-- AddThis Advanced Settings generic via filter on get_the_excerpt --><!-- AddThis Share Buttons generic via filter on get_the_excerpt --><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[47],"tags":[],"_links":{"self":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts\/636"}],"collection":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/comments?post=636"}],"version-history":[{"count":1,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts\/636\/revisions"}],"predecessor-version":[{"id":637,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/posts\/636\/revisions\/637"}],"wp:attachment":[{"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/media?parent=636"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/categories?post=636"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mouryasolutions.in\/blog\/wp-json\/wp\/v2\/tags?post=636"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}