triggers behaving differently in MYSQL 5.0 and NYSQl 5.5
Hi,
I have written a trigger after insert on table Cns_filemeta_data. The table description is appended, the trigger is as:
drop trigger if exists cfm_ai;
Delimiter $$
CREATE trigger cfm_ai
after insert on Cns_file_metadata
for each row
begin
insert into user_accounting.user_audit (entry_date,filesize,fileid,parent_fileid,last_accessed,last_modified,gid,filename,userDN,groupname) values (UNIX_TIMESTAMP(),new.filesize,new.fileid,new.parent_fileid,new.atime,new.mtime,new.gid,new.name,(select username from Cns_userinfo where userid = new.owner_uid),(select groupname from Cns_groupinfo where gid = new.gid));
end
$$
delimiter ;
Problem:
when the trigger executes in version 5.5 the table user_audit filesize field is updated accordingly but in version 5.0.7 the table user_audit field filesize holds value zero after the execution of triggers.
I am getting very confused what to do in order to resolve the issue, as the dependent table is required for accounting purposes and need to be updated.
The Table description is as.
Table: Cns_file_metadata
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| rowid | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| fileid | bigint(20) unsigned | YES | UNI | NULL | |
| parent_fileid | bigint(20) unsigned | YES | MUL | NULL | |
| guid | char(36) | YES | UNI | NULL | |
| name | varchar(255) | YES | | NULL | |
| filemode | int(10) unsigned | YES | | NULL | |
| nlink | int(11) | YES | | NULL | |
| owner_uid | int(10) unsigned | YES | | NULL | |
| gid | int(10) unsigned | YES | | NULL | |
| filesize | bigint(20) unsigned | YES | | NULL | |
| atime | int(11) | YES | | NULL | |
| mtime | int(11) | YES | | NULL | |
| ctime | int(11) | YES | | NULL | |
| fileclass | smallint(6) | YES | | NULL | |
| status | char(1) | YES | | NULL | |
| csumtype | varchar(2) | YES | | NULL | |
| csumvalue | varchar(32) | YES | | NULL | |
| acl | blob | YES | | NULL | |
+---------------+---------------------+------+-----+---------+----------------+
Table : user_audit (in other database)
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| rowid | int(10) unsigned | NO | PRI | NULL | auto_increment |
| entry_date | int(11) | YES | | NULL | |
| filesize | bigint(20) unsigned | YES | | NULL | |
| fileid | bigint(20) unsigned | YES | | NULL | |
| parent_fileid | bigint(20) unsigned | YES | MUL | NULL | |
| last_accessed | int(11) | YES | | NULL | |
| last_modified | int(11) | YES | | NULL | |
| gid | int(10) unsigned | YES | | NULL | |
| filename | varchar(255) | YES | | NULL | |
| userDN | varchar(255) | YES | | NULL | |
| groupname | varchar(20) | YES | | NULL | |
+---------------+---------------------+------+-----+---------+----------------+
Regards
Neetu Sharma