MySQL Forums
Forum List  »  Triggers

pls solve this trigger, it has some errors
Posted by: muneer ahmed
Date: July 26, 2006 11:43PM

table structure of data_transfer_cfg

/*Column Information For - cace_cdr_innodb.data_transfer_cfg*/
--------------------------------------------------------------

Field Type Collation Null Key Default Extra Privileges Comment
---------- ----------- --------------- ------ ------ ----------------- -------------- ------------------------------- -----------------------------------------------------------------------------------------------------
start_time datetime (NULL) YES (NULL) select,insert,update,references The beginning time of this record
end_time datetime (NULL) YES (NULL) select,insert,update,references the end time of this record
visit_id bigint(20) (NULL) YES MUL (NULL) select,insert,update,references
interval int(11) (NULL) YES (NULL) select,insert,update,references flowhseet interval
device_id int(11) (NULL) YES (NULL) select,insert,update,references identify device
id bigint(20) (NULL) NO PRI (NULL) auto_increment select,insert,update,references
flag tinyint(4) (NULL) NO select,insert,update,references to identify the transfer status of this record, 0- new record, 1-begin transfer, 2- transfer complete
updated_by varchar(20) utf8_general_ci YES (NULL) select,insert,update,references
updated_on timestamp (NULL) YES CURRENT_TIMESTAMP select,insert,update,references

/*Index Information For - cace_cdr_innodb.data_transfer_cfg*/
-------------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
----------------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
data_transfer_cfg 0 PRIMARY 1 id A 0 (NULL) (NULL) BTREE
data_transfer_cfg 1 visit_id 1 visit_id A 0 (NULL) (NULL) YES BTREE
data_transfer_cfg 1 visit_id 2 device_id A 0 (NULL) (NULL) YES BTREE

/*DDL Information For - cace_cdr_innodb.data_transfer_cfg*/
-----------------------------------------------------------

Table Create Table
----------------- -----------------------------------------------------------------------------------------------------------------------------------------------
data_transfer_cfg CREATE TABLE `data_transfer_cfg` (
`start_time` datetime default NULL COMMENT 'The beginning time of this record',
`end_time` datetime default NULL COMMENT 'the end time of this record',
`visit_id` bigint(20) default NULL,
`interval` int(11) default NULL COMMENT 'flowhseet interval',
`device_id` int(11) default NULL COMMENT 'identify device',
`id` bigint(20) NOT NULL auto_increment,
`flag` tinyint(4) NOT NULL COMMENT 'to identify the transfer status of this record, 0- new record, 1-begin transfer, 2- transfer complete',
`updated_by` varchar(20) default NULL,
`updated_on` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `visit_id` (`visit_id`,`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


table strucuture of audit_trial

*Column Information For - cace_cdr_innodb.audit_trial*/
--------------------------------------------------------

Field Type Collation Null Key Default Extra Privileges Comment
------------ ------------ --------------- ------ ------ ----------------- -------------- ------------------------------- -------
id bigint(20) (NULL) NO PRI (NULL) auto_increment select,insert,update,references
visit_id bigint(20) (NULL) YES (NULL) select,insert,update,references
user varchar(20) utf8_general_ci NO 0 select,insert,update,references
time timestamp (NULL) YES CURRENT_TIMESTAMP select,insert,update,references
source varchar(255) utf8_general_ci NO select,insert,update,references
modification text utf8_general_ci YES (NULL) select,insert,update,references

/*Index Information For - cace_cdr_innodb.audit_trial*/
-------------------------------------------------------

Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
----------- ---------- -------- ------------ ----------- --------- ----------- -------- ------ ------ ---------- -------
audit_trial 0 PRIMARY 1 id A 7 (NULL) (NULL) BTREE

/*DDL Information For - cace_cdr_innodb.audit_trial*/
-----------------------------------------------------

Table Create Table
----------- ------------------------------------------------------------------------------------
audit_trial CREATE TABLE `audit_trial` (
`id` bigint(20) NOT NULL auto_increment,
`visit_id` bigint(20) default NULL,
`user` varchar(20) NOT NULL default '0',
`time` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`source` varchar(255) NOT NULL default '',
`modification` text,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

create trigger `DATA_TRANSFER_CFG_AU` AFTER UPDATE on `data_transfer_cfg`
for each row BEGIN

declare source varchar(255) default 'DATA_TRANSFER_CFG';
declare modification text default '';

if(new.start_time is null AND old.start_time is not null) then
set modification = concat(modification, 'start_time: ', old.start_time, '/', ';');
elseif(new.start_time is not null AND old.start_time is null) then
set modification = concat(modification, 'start_time: ', '/', new.start_time, ';');
elseif(new.start_time != old.start_time) then
set modification = concat(modification, 'start_time: ', old.start_time, '/', new.start_time, ';');
end if;

if(new.end_time is null AND old.end_time is not null) then
set modification = concat(modification, 'end_time: ', old.end_time, '/', ';');
elseif(new.end_time is not null AND old.end_time is null) then
set modification = concat(modification, 'end_time: ', '/', new.end_time, ';');
elseif(new.end_time != old.end_time) then
set modification = concat(modification, 'end_time: ', old.end_time, '/', new.end_time, ';');
end if;

if(new.interval is null AND old.interval is not null) then
set modification = concat(modification, 'interval: ', old.interval, '/', ';');
elseif(new.interval is not null AND old.interval is null) then
set modification = concat(modification, 'interval: ', '/', new.interval, ';');
elseif(new.interval != old.interval) then
set modification = concat(modification, 'interval: ', old.interval, '/', new.interval, ';');
end if;

if(new.device_id is null AND old.device_id is not null) then
set modification = concat(modification, 'device_id: ', old.device_id, '/', ';');
elseif(new.device_id is not null AND old.device_id is null) then
set modification = concat(modification, 'device_id: ', '/', new.device_id, ';');
elseif(new.device_id != old.device_id) then
set modification = concat(modification, 'device_id: ', old.device_id, '/', new.device_id, ';');
end if;

if(new.flag is null AND old.flag is not null) then
set modification = concat(modification, 'flag: ', old.flag, '/', ';');
elseif(new.flag is not null AND old.flag is null) then
set modification = concat(modification, 'flag: ', '/', new.flag, ';');
elseif(new.flag != old.flag) then
set modification = concat(modification, 'flag: ', old.flag, '/', new.flag, ';');
end if;

insert into audit_trial (visit_id,`user`,source,modification) values(new.visit_id, new.updated_by, source, modification);
END;
$$

Options: ReplyQuote


Subject
Views
Written By
Posted
pls solve this trigger, it has some errors
2148
July 26, 2006 11:43PM


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.