|
ostalcode,new.shipping_address_country,new.parent_id,
new.sic_code,new.campaign_id,'U');
END||
删除触发器
delimiter ||
DROP TRIGGER IF EXISTS t_afterdelete_on_accounts ||
CREATE TRIGGER t_afterdelete_on_accounts
AFTER delete ON sugarcrm642ce.accounts
FOR EACH ROW
BEGIN
insert into sugarcrm642cebackup.accountsbackup
(`id`,
`name`,
`date_entered`,
`date_modified`,
`modified_user_id`,
`created_by`,
`description`,
`deleted`,
`assigned_user_id`,
`account_type`,
`industry`,
`annual_revenue`,
`phone_fax`,
`billing_address_street`,
`billing_address_city`,
`billing_address_state`,
`billing_address_postalcode`,
`billing_address_country`,
`rating`,
`phone_office`,
`phone_alternate`,
`website`,
`ownership`,
`employees`,
`ticker_symbol`,
`shipping_address_street`,
`shipping_address_city`,
`shipping_address_state`,
`shipping_address_postalcode`,
`shipping_address_country`,
`parent_id`,
`sic_code`,
`campaign_id`,
`BehaviorType`) values(old.id,old.name,old.date_entered,old.date_modified,old.modified_user_id,old.created_by,old.description,old.deleted,
old.assigned_user_id,old.account_type,old.industry,old.annual_revenue,old.phone_fax,old.billing_address_street,old.billing_address_city,old.billing_address_state,
old.billing_address_postalcode,old.billing_address_country,old.rating,old.phone_office,old.phone_alternate,old.website,old.ownership,old.employees,old.ticker_symbol,
old.shipping_address_street,old.billing_address_city,old.shipping_address_state,old.shipping_address_postalcode,old.shipping_address_country,old.parent_id,
old.sic_code,old.campaign_id,'D');
END||
? 上面必须增加behaviortype字段，因为如果是I代表插入，U代表更新，D代表删除，接着增加mysql的备份表，专门记录这三种操作。
?
CREATE TABLE `accountsbackup` (
`id` char(36) NOT NULL,
`name` varchar(150) DEFAULT NULL,
`date_entered` datetime DEFAULT NULL,
`date_modified` datetime DEFAULT NULL,
`modified_user_id` char(36) DEFAULT NULL,
`created_by` char(36) DEFAULT NULL,
`description` text,
`deleted` tinyint(1) DEFAULT NULL,
`assigned_user_id` char(36) DEFAULT NULL,
`account_type` varchar(50) DEFAULT NULL,
`industry` varchar(50) DEFAULT NULL,
`annual_revenue` varchar(100) DEFAULT NULL,
`phone_fax` varchar(100) DEFAULT NULL,
`billing_address_street` varchar(150) DEFAULT NULL,
`billing_address_city` varchar(100) DEFAULT NULL,
`billing_address_state` varchar(100) DEFAULT NULL,
`billing_address_postalcode` varchar(20) DEFAULT NULL,
`billing_address_country` varchar(255) DEFAULT NULL,
`rating` varchar(100) DEFAULT NULL,
`phone_office` varchar(100) DEFAULT NULL,
`phone_alternate` varchar(100) DEFAULT NULL,
`website` varchar(255) DEFAULT NULL,
`ownership` varchar(100) DEFAULT NULL,
`employees` varchar(10) DEFAULT NULL,
`ticker_symbol` varchar(10) DEFAULT NULL,
`shipping_address_street` varchar(150) DEFAULT NULL,
`shipping_address_city` varchar(100) DEFAULT NULL,
`shipping_address_state` varchar(100) DEFAULT NULL,
`shipping_address_postalcode` varchar(20) DEFAULT NULL,
`shipping_address_country` varchar(255) DEFAULT NULL,
`parent_id` char(36) DEFAULT NULL,
`sic_code` varchar(10) DEFAULT NULL, |