MySQL Forums
Forum List  »  Triggers

triggers behaving differently in MYSQL 5.0 and NYSQl 5.5
Posted by: neetu sharma
Date: May 09, 2012 12:43AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
triggers behaving differently in MYSQL 5.0 and NYSQl 5.5
2085
May 09, 2012 12:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.