[Digikam-devel] [Bug 286496] New: Trigger on update of field - not supported in MySQL

[Ignatius Reilly]

https://bugs.kde.org/show_bug.cgi?id=286496

           Summary: Trigger on update of field - not supported in MySQL
           Product: digikam
           Version: 2.3.0
          Platform: Ubuntu Packages
        OS/Version: Linux
            Status: UNCONFIRMED
          Severity: normal
          Priority: NOR
         Component: Database
        AssignedTo: digikam-devel at kde.org
        ReportedBy: ignatius.reilly at free.fr


Version:           2.3.0 (using KDE 4.7.2) 
OS:                Linux

In the SQLite DDL:

DROP TRIGGER IF EXISTS move_tagstree;
DELIMITER //
CREATE TRIGGER move_tagstree AFTER UPDATE OF pid ON Tags
 FOR EACH ROW BEGIN
                DELETE FROM TagsTree
                    WHERE
                    ((id = OLD.id)
                    OR
                    id IN (SELECT id FROM TagsTree WHERE pid=OLD.id))
                    AND
                    pid IN (SELECT pid FROM TagsTree WHERE id=OLD.id);
                INSERT INTO TagsTree
                    SELECT NEW.id, NEW.pid
                    UNION
                    SELECT NEW.id, pid FROM TagsTree WHERE id=NEW.pid
                    UNION
                    SELECT id, NEW.pid FROM TagsTree WHERE pid=NEW.id
                    UNION
                    SELECT A.id, B.pid FROM TagsTree A, TagsTree B
                    WHERE
                    A.pid = NEW.id AND B.id = NEW.pid;
 END
//
DELIMITER ;


In "AFTER UPDATE OF pid ON Tags", the "OF pid" part is not supported by MySQL
5.1 (can't specify a particular field that is updated, only that a record has
been updated as a whole)

Reproducible: Didn't try

Steps to Reproduce:
New SQLite DB - inspect the DDL

Actual Results:  
Don't know - I migrated from an older version previously migrated to MySQL. Run
through a comparison of the SQLite DDL and the MySQL one, where it appeared
that several triggers were missing in the MySQL verison

Expected Results:  
Using a MySQL back-end, the logic of changing a parent tag is seems to function
well in the code, without the need of this trigger.

I suggest to remove this trigger.

-- 
Configure bugmail: https://bugs.kde.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.