douji5329
douji5329
2015-01-04 00:37
浏览 21
已采纳

用MySQL创建触发器

Two tables: users and messages

 CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(10) NOT NULL,
`password` varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
`verifystring` varchar(20) NOT NULL,
`active` tinyint(4) NOT NULL,
`usertype` tinyint(4) NOT NULL,
`img` varchar(200) DEFAULT NULL,
 PRIMARY KEY (`id`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

 CREATE TABLE IF NOT EXISTS `messages` (
`id` tinyint(4) NOT NULL AUTO_INCREMENT,
`date` datetime NOT NULL,
`user_id` int(11) NOT NULL,
`topic_id` int(11) NOT NULL,
`subject` varchar(100) NOT NULL,
`body` text NOT NULL,
`img` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`id`),
 KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=51 ;

trigger:

CREATE TRIGGER `updateimg` AFTER UPDATE ON `users`
 FOR EACH ROW begin
    update messages set img = new.img where messages.user_id = users.id;
end

img field is keeping the name of the image user has uploaded. How can i copy the value img from table users to table messages when it is updated?

图片转代码服务由CSDN问答提供 功能建议

两个表:用户和消息

  CREATE TABLE IF NOT NOT  EXISTS`user`(
`id` int(11)NOT NULL AUTO_INCREMENT,
`username` varchar(10)NOT NULL,
`password` varchar(10)NOT NULL,
`email` varchar(100  )NOT NULL,
`verifystring` varchar(20)NOT NULL,
`active` tinyint(4)NOT NULL,
`usertype` tinyint(4)NOT NULL,
`img` varchar(200)DEFAULT  NULL,
 PRIMARY KEY(`id`)
)ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT = 8; 
 
 CREATE TABLE IF NOT NOT EXISTS`messages`(
`id` tinyint(4)NOT NULL AUTO_INCREMENT  ,
`date` datetime NOT NULL,
`user_id` int(11)NOT NULL,
`topic_id` int(11)NOT NULL,
`subject` varchar(100)NOT NULL,
`body  `text NOT NULL,
`img` varchar(200)DEFAULT NULL,
 PRIMARY KEY(`id`),
 KEY`topic_id`(`topic_id`)
)ENGINE = InnoDB DEFAULT CHARSET = latin1 AUTO_INCREMENT =  51; 
   
 
 

触发器:

  CREATE TRIGGER`updimimg`更新后`users` 
 FOR EACH 行开始
 upd 消息设置img = new.img其中messages.user_id = users.id; 
end 
   
 
 

img字段是保持用户上传的图像名称。 如何在更新时将表格用户的值img复制到表格消息中?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • douben7493
    douben7493 2015-01-04 01:19
    已采纳

    Thats should do it:

    CREATE TRIGGER update AFTER UPDATE ON table1
        for each ROW
        begin
            update table2 set y = new.y where table2.user_id = new.id;
        end
    
    点赞 评论

相关推荐