Database Transaction Auditing With MySQL and PostgreSQL Using Triggers

Discussion in 'PostgreSQL' started by pete_bisby, Nov 11, 2007.

  1. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    Logging database transactions is relatively easy within PostgeSQL and MySQL. Options can be set within the configuration files (postgresql.conf for PostgreSQL and my.ini for MySQL) to enable and record database INSERTs, UPDATEs and DELETEs. But if a client requires to view this information on a regular basis for auditing purposes, without the need for a database administrator, a different method is needed to record individual field changes. In this article we will look at the use of triggers for recording these transactions.

    To store the auditing transaction a table will be defined, which hold information relating to the table and field that triggered the event, the old value stored in the field before the triggered event, the new value that will be stored in the field after the triggered event, the user that made the change, and the date/time of the event. An example of the audit table definition follows. Note the differences with the syntax regarding auto-incrementing values for the primary key and declaring the primary key constraint itself.

    Code:
    -- MySQL
    CREATE TABLE `tblAudit` (
    [INDENT]`AuditId` int(8) NOT NULL auto_increment,
    `UserName` varchar(50) NOT NULL,
    `TableName` varchar(50) NOT NULL,
    `FieldName` varchar(50) NOT NULL,
    `OldValue` longtext,
    `NewValue` longtext,
    `DTAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
    PRIMARY KEY  (`AuditId`)[/INDENT]
    );
    
    -- PostgreSQL
    CREATE TABLE "tblAudit" (
    [INDENT]"AuditId" int4 NOT NULL DEFAULT nextval('"tblAudit_AuditId_seq"'::regclass),
    "UserName" varchar(50) NOT NULL,
    "TableName" varchar(50) NOT NULL,
    "FieldName" varchar(50) NOT NULL,
    "OldValue" text,
    "NewValue" text,
    "DTAdded" timestamp NOT NULL DEFAULT current_timestamp(),
    CONSTRAINT "tblAudit_pkey" PRIMARY KEY ("AuditId")[/INDENT]
    );
    
    A function/stored procedure will be used to populate the audit table, which will be referenced within the trigger statements. Again, note the differences how the two procedures reference the values being passed. The two dollar signs at the end of the MySQL procedure is a text delimeter, because semi-colons are used within the procedure itself. This applies to the $BODY$ delimeters used within the PostgreSQL function.

    Code:
    -- MySQL
    CREATE PROCEDURE `add_tblAudit`(IN pUserId VARCHAR(50), IN pTableName VARCHAR(50), IN pFieldName VARCHAR(50), IN pOldValue LONGTEXT, IN pNewValue LONGTEXT)
    BEGIN
    [INDENT]INSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`)
    VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue);
    RETURN LAST_INSERT_ID() AS `AuditId`;[/INDENT]
    END;$$
    
    -- PostgreSQL
    CREATE OR REPLACE FUNCTION "add_tblAudit"("varchar", "varchar", "varchar", text, text) RETURNS int4 AS$BODY$
    BEGIN
    [INDENT]INSERT INTO "tblAudit" ("AuditId", "UserName", "TableName", "FieldName", "OldValue", "NewValue")
    VALUES (nextval('"tblAudit_AuditId_seq"'), $1, $2, $3, $4, $5, $6);
    RETURN currval('"tblAudit_AuditId_seq"');[/INDENT]
    END$BODY$
    LANGUAGE 'plpgsql' VOLATILE;
    
    For the purpose of this article a simple table will be constructed, which will allow us to define triggers for the table events.

    Code:
    -- MySQL
    CREATE TABLE `tblTest` (
    [INDENT]`TestId` int(8) NOT NULL DEFAULT auto_increment,
    `TestVarchar` varchar(50),
    `TestNumber` int(4) NOT NULL default '0',
    `TestDate` DATE NOT NULL default CURRENT_DATE,
    PRIMARY KEY (`TestId`)[/INDENT]
    );
    
    -- PostgreSQL
    CREATE TABLE `tblTest` (
    [INDENT]`TestId` int4 NOT NULL DEFAULT nextval('"tblAudit_AuditId_seq"'::regclass),
    `TestVarchar` varchar(50),
    `TestNumber` int4 NOT NULL default '0',
    `TestDate` DATE NOT NULL default current_date,
    CONSTRAINT "tblTest_pkey" PRIMARY KEY (`TestId`)[/INDENT]
    );
    

    Trigger Definition in MySQL



    The syntax for declaring a trigger on a MySQL table is as follows:

    Code:
    CREATE TRIGGER [I]trigger_name[/I] [I]trigger_time[/I] [I]trigger_event[/I] ON [I]table_name[/I]
    FOR EACH ROW [I]trigger_statement[/I]
    
    Where trigger_name is the name of the trigger, trigger_time is either BEFORE or AFTER the event, trigger_event is either INSERT, UPDATE or DELETE, table_name is the name of the table the trigger is going to be placed on, and trigger_statement is any valid SQL statement(s).

    With MySQL a trigger will need to be defined for each of the INSERT, UPDATE and DELETE events on the table. MySQL will distinguish the status of the record before and after the event by using the OLD and NEW keywords within the trigger statements. We will place "--new record--" in the OldValue field for INSERT events, and "--deleted record--" in the NewValue field for DELETE events. You will have to be aware that for the UPDATE trigger event we had to place additional checks in for comparing the OLD and NEW data, especially for fields that already contained a NULL value. If not, an error will appear and nothing will be written to the Audit table.

    Code:
    -- Create INSERT event for tblTest
    CREATE OR REPLACE trigger_insert_tblTest AFTER INSERT ON tblTest
    FOR EACH ROW
    [INDENT]BEGIN
    		
    IF (NEW.TestVarchar <> '') OR (NEW.TestVarchar IS NOT NULL) THEN
    [INDENT]CALL add_tblAudit (USER(), "tblTest", "TestVarchar", "--new record--", NEW.TestVarchar);[/INDENT]
    END IF;
    
    IF (NEW.TestNumber <> 0) THEN
    [INDENT]CALL add_tblAudit (USER(), "tblTest", "TestNumber", "--new record--", NEW.TestNumber);[/INDENT]
    END IF;
    
    IF (NEW.TestDate <> '') OR (NEW.TestDate IS NOT NULL) THEN
    [INDENT]CALL add_tblAudit (USER(), "tblTest", "TestDate", "--new record--", NEW.TestDate);[/INDENT]
    END IF;[/INDENT]
    
    END;$$
    
    -- Create UPDATE event for tblTest
    CREATE OR REPLACE trigger_update_tblTest AFTER UPDATE ON tblTest
    FOR EACH ROW
    [INDENT]BEGIN
    		
    IF (NEW.TestVarchar <> OLD.TextVarchar) OR (NEW.TestVarchar IS NOT NULL AND OLD.TextVarchar IS NULL) OR (NEW.TestVarchar IS NULL AND OLD.TextVarchar IS NOT NULL) THEN
    [INDENT]CALL add_tblAudit (USER(), "tblTest", "TestVarchar", OLD.TestVarchar, NEW.TestVarchar);[/INDENT]
    END IF;
    
    IF (NEW.TestNumber <> OLD.TestNumber) THEN
    [INDENT]CALL add_tblAudit (USER(), "tblTest", "TestNumber", OLD.TestNumber, NEW.TestNumber);[/INDENT]
    END IF;
    
    IF (NEW.TestDate <> OLD.TestDate) OR (NEW.TestDate IS NOT NULL AND OLD.TestDate IS NULL) OR (NEW.TestDate IS NULL AND OLD.TestDate IS NOT NULL) THEN
    [INDENT]CALL add_tblAudit (USER(), "tblTest", "TestDate", OLD.TestDate, NEW.TestDate);[/INDENT]
    END IF;[/INDENT]
    		
    END;$$
    
    -- Create DELETE event for tblTest
    CREATE OR REPLACE trigger_update_tblTest AFTER DELETE ON tblTest
    FOR EACH ROW
    [INDENT]BEGIN
    		
    CALL add_tblAudit (USER(), "tblTest", "TestVarchar", OLD.TestVarchar, "--deleted record--");
    CALL add_tblAudit (USER(), "tblTest", "TestNumber", OLD.TestNumber, "--deleted record--");
    CALL add_tblAudit (USER(), "tblTest", "TestDate", OLD.TestDate, "--deleted record--);
    [/INDENT]		
    END;$$
    

    Trigger Definition in PostgreSQL



    Now with PostgreSQL it is more of a two-stage process. The trigger event is created against the table you wish to audit, which will call/execute a procedure for comparing the values of the record before and after the event. The trigger definition for PostgreSQL can combine all of the INSERT, UPDATE and DELETE events, whereas in MySQL the definitions for the events had to be declared separately. The syntax for declaring a trigger in PostgreSQL is as follows:

    Code:
    CREATE TRIGGER [I]trigger_name[/I] { BEFORE | AFTER } { [I]trigger_event[/I] [ OR ... ] }ON [I]table_name[/I] [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE [I]procedure_name (arguments)[/I]
    
    Where trigger_name is the name of the trigger, trigger_event is either INSERT, UPDATE or DELETE, which can be separated by including an OR between each trigger event, table_name is the name of the table the trigger is going to be placed on, and procedure_name (arguments) is the stored procedure that will be executed when an event is triggered.

    Because PostgreSQL can allow us to define the three events within the same trigger, the definition can be as follows:

    Code:
    CREATE TRIGGER trigger_tblTest AFTER INSERT OR UPDATE OR DELETE ON "tblTest"
    FOR EACH ROW EXECUTE PROCEDURE "sp_audit_tblTest";
    
    Now that PostgreSQL allows us to define all the events together within the same trigger, it needs to be able to distinguish between each of the events when they transpire. PostgreSQL has a special variable that will allow us to do this - TG_OP - and combine all of the events within one single procedure.

    Code:
    CREATE OR REPLACE FUNCTION "sp_audit_tblTest"() RETURNS "trigger" AS$BODY$
    [INDENT]BEGIN
    [INDENT]IF (TG_OP = 'INSERT') THEN
    [INDENT]IF (NEW."TestVarchar" <> '') OR (NEW."TestVarchar" IS NOT NULL) THEN
    [INDENT]PERFORM "add_tblAudit"(current_user, 'tblTest', 'TestVarchar', '--new record--', NEW."TestVarchar");[/INDENT]
    END IF;[/INDENT]
    [INDENT]IF (NEW."TestNumber" <> '0') THEN
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", "--new record--", NEW."TestNumber");[/INDENT]
    END IF;[/INDENT]
    [INDENT]IF (NEW."TestDate" <> '') OR (NEW."TestDate" IS NOT NULL) THEN
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", "--new record--", NEW."TestDate");[/INDENT]
    END IF;[/INDENT]
    [INDENT]RETURN NEW;[/INDENT]
    ELSIF (TG_OP = 'UPDATE') THEN
    [INDENT]IF (NEW."TestVarchar" <> OLD."TextVarchar") OR (NEW."TestVarchar" IS NOT NULL AND OLD."TextVarchar" IS NULL) OR (NEW."TestVarchar" IS NULL AND OLD."TextVarchar" IS NOT NULL) THEN
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", NEW."TestVarchar");[/INDENT]
    END IF;[/INDENT]
    [INDENT]IF (NEW."TestNumber" <> OLD."TestNumber") THEN
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", OLD."TestNumber", NEW."TestNumber");[/INDENT]
    END IF;[/INDENT]
    [INDENT]IF (NEW."TestDate" <> OLD."TestDate") OR (NEW."TestDate" IS NOT NULL AND OLD."TestDate" IS NULL) OR (NEW."TestDate" IS NULL AND OLD."TestDate" IS NOT NULL) THEN
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", OLD."TestDate", NEW."TestDate");[/INDENT]
    END IF;[/INDENT]
    [INDENT]RETURN NEW;[/INDENT]
    ELSIF (TG_OP = 'DELETE') {
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestVarchar", OLD."TestVarchar", '--deleted record--');[/INDENT]
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestNumber", OLD."TestNumber", '--delete record--');[/INDENT]
    [INDENT]PERFORM "add_tblAudit"(current_user, "tblTest", "TestDate", OLD."TestDate", '--deleted record--');[/INDENT]
    [INDENT]RETURN OLD;[/INDENT]
    END IF;
    RETURN NULL;[/INDENT]
    END;$BODY$
    LANGUAGE 'plpgsql' VOLATILE;
    
    With the triggers in place, on as many tables as you wish, any information being added, updated or deleted, can be recorded into a single table. From here the client will be able to query and view all data changes, and be able to search on which user performed the event, at what specific date/time, and the old and new value of the field that was changed.
     
    shabbir likes this.
  2. coderzone

    coderzone Super Moderator

    Joined:
    Jul 25, 2004
    Messages:
    736
    Likes Received:
    38
    Trophy Points:
    28
  3. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,375
    Likes Received:
    388
    Trophy Points:
    83
  5. ash0279

    ash0279 New Member

    Joined:
    Jan 24, 2008
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Database Administrator; Data Architect
    Location:
    Manila Philippines
    I like this one.. seems my PL/SQL knowledge would be still usable for MySQL language extension.
     
  6. bjdahlstrom

    bjdahlstrom New Member

    Joined:
    Nov 14, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Pete, you have piqued my curiosity. I need this audit process on my current PostgreSQL application. It really looks like what I need. Is the coding you show above compatible with unix (specifically solaris)? Or is it only for PC platforms only? I really need help on this my expertise on PostgreSQL is extremely limited. Thanks in advance for your assistance. Bernie D.
     
  7. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    Everything is done within PostgreSQL and MySQL - the platform/operating system is independent. So if you are using Unix/Solaris with PostgreSQL installed, these triggers/functions will work fine.
     
  8. bjdahlstrom

    bjdahlstrom New Member

    Joined:
    Nov 14, 2008
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Mr. Bisby, thank you for the rapid response. I have one additional question if I can further intrude upon your time.

    There is much written on the web concerning storing functions in PostgreSQL, but from my perspective these articles are far too esoteric for my experience level.

    I simply want to know how I can see the details of an existing/already stored function in PostgreSQL.

    Again, I am sorry for the intrusion and I thank you in advance. BernieD
     
  9. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    Functions are stored within the pg_proc table - you will need to use the following query:

    SELECT * FROM pg_proc WHERE proname='your_function_name';

    Alternatively, use either pgAdmin III (application/desktop) or phpPgAdmin (web browser based) - both open source management tools for PostgreSQL and both excellent for viewing functions and stored procedures.
     
  10. mugnebot

    mugnebot New Member

    Joined:
    Feb 16, 2009
    Messages:
    13
    Likes Received:
    1
    Trophy Points:
    3
    Home Page:
    http://www.sirinevlerhamami.com
    A solution like this does will not work with statement-based replication where the call to user() returns an empty string when the trigger runs on the slave. Any suggestions for how to work around that "feature"?
     
  11. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    Have each record within the table that is being audited have "user_name_created" and "user_name_modifed" fields. This information would then be passed through the trigger, which can then be recorded in the auditing table.
     
  12. alfsys

    alfsys New Member

    Joined:
    Mar 14, 2009
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    Hi

    I try this code:
    CREATE PROCEDURE `add_tblAudit`(IN pUserId VARCHAR(50), IN pTableName VARCHAR(50), IN pFieldName VARCHAR(50), IN pOldValue LONGTEXT, IN pNewValue LONGTEXT)
    BEGIN
    INSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`)
    VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue);
    RETURN LAST_INSERT_ID() AS `AuditId`;​
    END;$$

    And the line "RETURN LAST_INSERT_ID() AS `AuditId`;" gives an error 1064
    Any help?

    Alejandro
     
  13. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    You don't need to name/alias the returning value [LAST_INSERT_ID()].

    Also check the version of MySQL you are using - error 1064 is when a reserved word that was allowed in a previous version is now deprecated. Maybe using aliasing on a returned value is not allowed.
     
  14. itsys

    itsys Banned

    Joined:
    May 2, 2009
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    0
    Hi,

    Good knowledge of Mysql..............
     
  15. praveen222

    praveen222 New Member

    Joined:
    Mar 22, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Code:
    CREATE  trigger_insert_tbltest AFTER INSERT ON tbltest
    FOR EACH ROW
    
        IF (NEW.TestVarchar <> '') OR (NEW.TestVarchar IS NOT NULL) THEN
    
            CALL add_tblaudit (USER(), "tblTest", "TestVarchar", "--new record--", NEW.TestVarchar);
    
        END IF; IF (NEW.TestNumber <> 0) THEN
    
            CALL add_tblaudit (USER(), "tblTest", "TestNumber", "--new record--", NEW.TestNumber);
    
        END IF; IF (NEW.TestDate <> '') OR (NEW.TestDate IS NOT NULL) THEN
    
            CALL add_tblaudit (USER(), "tblTest", "TestDate", "--new record--", NEW.TestDate);
    
        END IF;
    The above code is not working in mysql 5.4.1. Especially it doesn't support Begin and END statements. and how can write if conditions.
    My intension is to find the audit details of delete, insert or modify records in mysql.
    I have successfully done with only updation, but my intension is to track the audit details when delete, insert and updation is done.

    The below code for tracking the updation records.



    Code:
      CREATE  TRIGGER before_employee_update 
     BEFORE UPDATE ON employees
     FOR EACH ROW 
     INSERT INTO employees_audit
     SET action = 'update OR insert OR delete OR select',
     employeeNumber = OLD.employeeNumber,
     lastname_old = OLD.lastname,
     lastname_new = NEW.lastname,
     user = CURRENT_USER(),
     changedon = NOW(); 
    please provide me of tracking of insertion, deletion and updation, related with above code.

    Thanks in Advance,

    Praveen
     
    Last edited by a moderator: Mar 23, 2010
  16. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    Firstly, MySQL 5.4.1 does support BEGIN and END within stored procedures - it has to because if you use mutliple SQL statements MySQL has to differentiate between the stored procedure and the SQL statement itself. So, the first chunk of code you have provided should work fine when you put back in the BEGIN and END keywords. If you're only using a single statement, which is what you have done in your second chunk of code, you don't need the BEGIN and END keywords - they are required for multiple statements.

    You're also missing the keyword TRIGGER from your CREATE statement.

    You will need to use a different delimiter when entering a stored procedure containing multiple statements. When I use phpMyAdmin I use the semi-colon for separating the multiple statements and use the pipe symbol ( | ) at the end of the stored procedure to tell MySQL that this is the end of the whole statement.

    You haven't included the actual trigger code in your question - if no-one can review your SQL statement it does make it quite difficult for anyone to provide any help. Bear in mind, however, that the trigger events have to be declared seaprately - you cannot combine INSERT, UPDATE and DELETE events into a single trigger in MySQL.

    I think the first port of call would be to go back to the MySQL Manual and look at the syntax for creating triggers (http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html). Then, if you're still stuck, come back and post your actual trigger statements on the forum.
     
  17. praveen222

    praveen222 New Member

    Joined:
    Mar 22, 2010
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Thanks for giving reply, so the if end if; keywords also not working. In which version of MYSQL supports stored procedures. I have a doubt regarding triggers that, I am created seperate triggers for insert, update, delete, but what is the difference between after and before for all above statements (insert/update/delete). and also after i modify two values in one table, shows seperate modifications in audit log table (with different rows).

    Thanks in Advance,

    Praveen.
     
  18. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    The IF ... END IF statements do work in MySQL stored prodecures, since the start of version 5 (probably before then). IF ... THEN ... ELSE statements are a fundamental part of controlling the process flow within any programming language - SQL, in all of its different guises, is no different.

    You have to create individual triggers for INSERT, UPDATE and DELETE, because MySQL does not allow the grouping of these actions together; unlike PostgreSQL, which does. I don't know why - it is just the way MySQL performs. Is it a good or bad thing? ... open up a debate and see what other developer's viewpoint is.

    The BEFORE and AFTER clauses within the trigger tell it when to run - BEFORE or AFTER the action. This was mentioned in the MySQL Manual (http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html) I put in my previous post - look at the "trigger_time" definition.

    The whole purpose of the auditing table was to show the inserts, updates and deletes of information relating to a specific database table. It shows traceability on a specific field on a row, as opposed to recording the whole row. You can, if you wanted to, write the whole row of information on creation/update within an audit table, showing the maturity/version control of a row. The only downside to this method would be that you would need a separate audit table for each database table in your database; unless you serialised the information before writing it to the audit table ... but this is probably going slgihtly off-piste!!

    The article shows "one" method of auditing transactions using triggers - there are plenty out there. It is up to the database developer to review all possibilities and implement a solution that meets the requirements of the project.

    Oh, and just one more comment - you are aware that MySQL version 5.4.1 is still classed as a "development" version. For stability purposes (certainly if you're placing this database into a production environment) I would recommend downgrading to 5.1.45. However, 5.4.1 still supports stored procedures (with or without BEGIN and END), IF-THEN-ELSE statements and triggers.
     
  19. chengui

    chengui New Member

    Joined:
    Jun 15, 2011
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    I'm currently writing this up, I haven't gotten to the testing it yet but I believe their are two errors in your script for the mysql section.

    The first is in the created procedure
    Quote:
    Code:
    CREATE PROCEDURE `add_tblAudit`(IN pUserId VARCHAR(50), IN pTableName VARCHAR(50), IN pFieldName VARCHAR(50), IN pOldValue LONGTEXT, IN pNewValue LONGTEXT)
    BEGIN
    INSERT INTO `tblAudit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`)
    VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue);
    Since in the parameter you use in add_tblAudit is 'pUserId' should the value in the insert statement also be pUserid and not pUserName? (Although I see you are using User() as the passed variable perhaps this is why?)

    Secondly in your trigger:

    Code:
    -- Create INSERT event for tblTest
    CREATE OR REPLACE trigger_insert_tblTest AFTER INSERT ON tblTest
    FOR EACH ROW
    BEGIN
    Code:
    -- Create UPDATE event for tblTest
    CREATE OR REPLACE trigger_update_tblTest AFTER UPDATE ON tblTest
    FOR EACH ROW
    BEGIN
    Code:
    -- Create DELETE event for tblTest
    CREATE OR REPLACE[COLOR="Red"] trigger_update_tblTest[/COLOR] AFTER DELETE ON tblTest
    FOR EACH ROW
    BEGIN
    I believe you are deleting your update trigger with the delete trigger in your example.
     
  20. pete_bisby

    pete_bisby New Member

    Joined:
    Nov 11, 2007
    Messages:
    48
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer/Analyst, PB
    Location:
    Manchester, England
    Home Page:
    http://www.pbworld.co.uk
    Yes, you're right - but the important thing here is the structure of the SQL, and not the specifics ...

    ... nobody's perfect !!
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice