我想创建一个触发器，在数据插入触发器更新非主或主在另一个表的数量

Question

我想创建一个触发器，其中在数据插入相同的userid，触发器更新数量1，在另一个表的特定行

CREATE OR REPLACE TRIGGER test_after_insert
AFTER INSERT
ON visitor
FOR EACH ROW
WHEN (old.visresid = new.visresid)
BEGIN
UPDATE visqty
SET primaryvisqty = primaryvisqty + 1
WHERE old.visresid = new.visresid
DBMS_OUTPUT.put_line ('Table updated');
END;

Answer 1

应该是

CREATE OR REPLACE TRIGGER test_after_insert
AFTER INSERT ON visitor
FOR EACH ROW
BEGIN
UPDATE visqty
SET primaryvisqty = primaryvisqty + 1
WHERE visresid = :new.visresid;
END;

• removeWHENclause
修正WHERE条款
• 用分号终止UPDATE语句
• 删除DBMS_OUTPUT调用(客户端，不支持它，将不会看到任何东西)

---

有样例表:

SQL> CREATE TABLE visitor
2  (
3     visresid   NUMBER
4  );
Table created.
SQL> CREATE TABLE visqty
2  (
3     visresid        NUMBER,
4     primaryvisqty   NUMBER
5  );
Table created.

当触发器更新可视性表时，这意味着它预先填充了数据:

SQL> INSERT INTO visqty (visresid, primaryvisqty)
2       VALUES (1, 0);
1 row created.

触发:

SQL> CREATE OR REPLACE TRIGGER test_after_insert
2     AFTER INSERT
3     ON visitor
4     FOR EACH ROW
5  BEGIN
6     UPDATE visqty
7        SET primaryvisqty = primaryvisqty + 1
8      WHERE visresid = :new.visresid;
9  END;
10  /
Trigger created.

测试:

SQL> INSERT INTO visitor (visresid)
2       VALUES (1);
1 row created.
SQL> SELECT * FROM visqty;
VISRESID PRIMARYVISQTY
---------- -------------
1             1
SQL>

---

如果visqty没有预先填充，则使用merge代替update:

SQL> CREATE OR REPLACE TRIGGER test_after_insert
2     AFTER INSERT
3     ON visitor
4     FOR EACH ROW
5  BEGIN
6     MERGE INTO visqty a
7          USING (SELECT :new.visresid AS visresid FROM DUAL) b
8             ON (a.visresid = b.visresid)
9     WHEN MATCHED
10     THEN
11        UPDATE SET a.primaryvisqty = a.primaryvisqty + 1
12     WHEN NOT MATCHED
13     THEN
14        INSERT     (visresid, primaryvisqty)
15            VALUES (:new.visresid, 1);
16  END;
17  /
Trigger created.
SQL>

Answer 2

:old.visresid将始终是INSERT上的NULL，因此您的过滤器WHERE :old.visresid = :new.visresid(纠正:NEW和:OLD绑定变量上缺失的:)将永远不会匹配任何内容。

如果您想为visitor中的每一行添加一个visqty中的行，那么您可以使用:

CREATE OR REPLACE TRIGGER test_after_insert
AFTER INSERT ON visitor
FOR EACH ROW
BEGIN
UPDATE visqty
SET    primaryvisqty = primaryvisqty + 1;
END;
/

db<此处小提琴>

---

因为我有布尔条件，我会根据0或1插入一个数据到特定的字段，我怎么能实现这一点?例如，如果primvis = 1，那么SET primaryvisqty = primaryvisqty + 1，如果primvis = 0，那么non primaryvisqty = primaryvisqty + 1?

CREATE OR REPLACE TRIGGER test_after_insert
AFTER INSERT ON visitor
FOR EACH ROW
BEGIN
IF :NEW.primvis = 1 THEN
UPDATE visqty
SET    primaryvisqty = primaryvisqty + 1;
ELSE
UPDATE visqty
SET    nonprimaryvisqty = nonprimaryvisqty + 1;
END IF;
END;
/

db<此处小提琴>

---

你可以使用触发器:

CREATE OR REPLACE TRIGGER test_after_insert
AFTER INSERT ON visitor
FOR EACH ROW
BEGIN
MERGE INTO visqty a
USING DUAL b
ON (a.permvisid = :NEW.visresid)
WHEN MATCHED THEN
UPDATE
SET primaryvisqty = primaryvisqty + :new.isprimary,
othervisqty   = othervisqty   + 1 - :new.isprimary
WHEN NOT MATCHED THEN
INSERT (permvisid, primaryvisqty, othervisqty)
VALUES (:new.visresid, :new.isprimary, 1 - :new.isprimary);
END;
/

但是看起来你真正想要的是一个视图，而不是一个表和一个触发器:

CREATE VIEW visqty(permvisid, primaryvisqty, othervisqty) AS
SELECT r.rid,
COUNT(CASE v.isprimary WHEN 1 THEN 1 END) AS primaryvisqty,
COUNT(CASE v.isprimary WHEN 0 THEN 1 END) AS othervisqty
FROM   residents r
LEFT OUTER JOIN visitor v
ON (r.rid = v.visresid)
GROUP BY r.rid

db<此处小提琴>