如何为3张表建立触发器
我对 MySql 有问题,我尝试为表编写触发器,但我对它的了解几乎为零。 我想做什么?当有人在表 B 中放入某些内容时,我想更新表 A 中的一些记录。但要做到这一点,我首先需要对表 B 中值为 1 和 -1 的行进行计数。 这是我的3张桌子
CREATE TABLE IF NOT EXISTS `wp_comment_vote` (
`vote_id` int(11) NOT NULL AUTO_INCREMENT,
`comment_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`value` int(11) NOT NULL,
`meta_id` int(11) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`vote_id`)
)
CREATE TABLE IF NOT EXISTS `wp_comments` (
`comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',,
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0'
`comment_author` tinytext NOT NULL,
`comment_author_email` varchar(100) NOT NULL DEFAULT '',
`comment_author_url` varchar(200) NOT NULL DEFAULT '',
`comment_author_IP` varchar(100) NOT NULL DEFAULT '',
`comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_content` text NOT NULLN',
PRIMARY KEY (`comment_ID`)
)
CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`)
)
delimiter //
CREATE TRIGGER countvotecomment AFTER UPDATE ON wp_comment_vote
FOR EACH ROW BEGIN
UPDATE wp_commentmeta
IF wp_commentmeta.meta_key = 'vote_comment_in_minus' THAN
SET wp_commentmeta.meta_value = (SELECT COUNT( value ) as glosy FROM wp_comment_vote WHERE comment_id=wp_commentmeta.comment_id AND value = '-1');
ELSEIF wp_commentmeta.meta_key = 'vote_comment_in_plus' THAN
SET wp_commentmeta.meta_value = (SELECT COUNT( value ) as glosy FROM wp_comment_vote WHERE comment_id=wp_commentmeta.comment_id AND value = '1')
END;
delimiter ;
I have problem with MySql, i try write triggers for table but my knowlage abut it is nearly none.
What i want to do? I want update some of record in table A when someone put something in table B. But to do that first i need count row with value 1 and -1 from table B.
This is my 3 table
CREATE TABLE IF NOT EXISTS `wp_comment_vote` (
`vote_id` int(11) NOT NULL AUTO_INCREMENT,
`comment_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`value` int(11) NOT NULL,
`meta_id` int(11) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`vote_id`)
)
CREATE TABLE IF NOT EXISTS `wp_comments` (
`comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',,
`user_id` bigint(20) unsigned NOT NULL DEFAULT '0'
`comment_author` tinytext NOT NULL,
`comment_author_email` varchar(100) NOT NULL DEFAULT '',
`comment_author_url` varchar(200) NOT NULL DEFAULT '',
`comment_author_IP` varchar(100) NOT NULL DEFAULT '',
`comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`comment_content` text NOT NULLN',
PRIMARY KEY (`comment_ID`)
)
CREATE TABLE IF NOT EXISTS `wp_commentmeta` (
`meta_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`comment_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`meta_key` varchar(255) DEFAULT NULL,
`meta_value` longtext,
PRIMARY KEY (`meta_id`),
KEY `comment_id` (`comment_id`),
KEY `meta_key` (`meta_key`)
)
delimiter //
CREATE TRIGGER countvotecomment AFTER UPDATE ON wp_comment_vote
FOR EACH ROW BEGIN
UPDATE wp_commentmeta
IF wp_commentmeta.meta_key = 'vote_comment_in_minus' THAN
SET wp_commentmeta.meta_value = (SELECT COUNT( value ) as glosy FROM wp_comment_vote WHERE comment_id=wp_commentmeta.comment_id AND value = '-1');
ELSEIF wp_commentmeta.meta_key = 'vote_comment_in_plus' THAN
SET wp_commentmeta.meta_value = (SELECT COUNT( value ) as glosy FROM wp_comment_vote WHERE comment_id=wp_commentmeta.comment_id AND value = '1')
END;
delimiter ;
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
触发器应该如下所示:
它不是以 100% 结束,因为我需要为 vote_comment_in_minus 添加第二个更新,但它有效!
Trigger should looks like this:
It's not end in 100% because I need add second UPDATE for vote_comment_in_minus but it works!