mysql - 当在 mydb.titles 中插入一行时，我想创建一个触发器来更新其他表 mydb.authors 中的字段

Question

我想创建一个触发器来更新其他表 mydb.authors 中的字段，当在 mydb.titlesPlease 中插入一行时请在下面找到我的代码

create trigger mydb.t_count 
on mydb.titles
after insert 
as
declare @bcount int,@aid int(8);
select @bcount=count(*) from mydb.titles;
select @aid=author_id from mydb.titles;

if(@bcount>0)
begin
update table  mydb.authors set titles_count=@bcount where author_id=@aid;
end//
else
begin
insert into mydb.authors values(author_id,titles_count) values(@aid,@bcount);
end
delimiter;

Answer 1

您问题的正确触发条件:

DELIMITER //
create trigger mydb.t_count 
after insert 
on mydb.titles
for each row begin
declare l_aid BIGINT UNSIGNED;-- should fit the length of author_id

SET l_aid = NEW.author_id;

-- first checking, if there is already an entry in the authors table:
if EXISTS(SELECT author_id FROM mydb.authors WHERE author_id = l_aid) THEN
-- if yes then update
update mydb.authors set titles_count=titles_count + 1 where author_id=l_aid;
else -- else insert with counter 1.
insert into mydb.authors (author_id,titles_count) values(l_aid,1);
END IF;
end //
DELIMITER ;

为此我使用了下表定义:

CREATE DATABASE `mydb` /*!40100 DEFAULT CHARACTER SET utf8 */;
use `mydb`;


create table authors (
    author_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(200),
    titles_count BIGINT
);

create table titles (
    title_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    author_id BIGINT UNSIGNED,
    book_name vARCHAR(200),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

仅使用一个触发器可能不适合，当您更新或删除标题时，标题计数尚未更新。

但是，有一个没有任何触发器的解决方案:

SELECT count(t.title_id) AS titles_count,t.author_id
FROM titles t
GROUP BY t.author.id;