通过使用触发器比较两个表的值来更新第三个表

Question

嗨，

我有三个表（Table1、Table2、Table3）。所有三个表都有相同的列名称（Company、License_count）。当 table2 中发生更新时，触发器将比较 table2 和 table2 的值。表1并将差异插入表三。我只需要对单行执行此操作。

有人可以帮忙吗？

Answer 1

好吧，我明白了。

drop trigger if exists table1and2difference;
CREATE TRIGGER table1and2difference
AFTER UPDATE ON Table2
FOR EACH ROW
  INSERT INTO Table3(Company, License_count)values
    (new.Company, (SELECT (SUM(License_count1) - License_count2) as License_count FROM
                   (SELECT Company as company1, License_count as License_count1 FROM 
                           Table1 WHERE Company=new.Company) AS t1
                   INNER JOIN
                   (SELECT Company as company2, License_count as License_count2 FROM
                           Table2 WHERE Company=new.Company) As t2
                   On company1=company2));

作为参考，这些是我用来测试触发器的值。

create table Table1(
Company VARCHAR(100) NOT NULL,
License_count INT NOT NULL);

insert into Table1(Company,License_count)values('Test_company',10);

create table Table2(
Company VARCHAR(100) NOT NULL,
License_count INT NOT NULL);

insert into Table2(Company,License_count)values('Test_company',8);

create table Table3(
Company VARCHAR(100) NOT NULL,
License_count INT NOT NULL);

drop trigger if exists table1and2difference;
CREATE TRIGGER table1and2difference
AFTER UPDATE ON Table2
FOR EACH ROW
     INSERT INTO Table3(Company, License_count)values
         (new.Company, (SELECT (SUM(License_count1) - License_count2) as License_count FROM
                            (SELECT Company as company1, License_count as License_count1 FROM Table1 WHERE Company=new.Company) AS t1
                            INNER JOIN
                            (SELECT Company as company2, License_count as License_count2 FROM Table2 WHERE Company=new.Company) As t2
                            On company1=company2));

update Table2 set License_count=7;