我payment_detail基于另外兩個表（客戶和付款）創建了一個表，如下所示：
CREATE TABLE IF NOT EXISTS payment_detail AS
SELECT customer.customer_id,
customer.first_name || ' ' || customer.last_name AS customer_name,
to_char(payment_date, 'Mon YYYY') AS month_yr,
SUM(payment.amount) AS total_amount,
COUNT(payment.amount) AS payments_count
FROM payment
INNER JOIN customer ON payment.customer_id = customer.customer_id
GROUP BY 1,3
ORDER BY
MAX(date_trunc('year', payment_date)) DESC,
MAX(date_trunc('month', payment_date)) DESC,
MAX(amount) DESC;
結果表如下所示：
目標：創建觸發器函式以payment_detail在 UPDATE 和 INSERT 操作時更新表。
UPDATE 操作的觸發器：
CREATE OR REPLACE FUNCTION update_payment_detail()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
UPDATE payment_detail SET
customer_id = NEW.customer_id,
customer_name = first_name || ' ' || last_name,
month_yr = to_char(NEW.payment_date, 'Mon YYYY'),
FROM
customer
WHERE
customer.customer_id = NEW.customer_id AND
payment_detail.customer_id = OLD.customer_id;
RETURN NULL;
END;
$$
CREATE TRIGGER update_payment_detail
AFTER UPDATE
ON payment
FOR EACH ROW
EXECUTE PROCEDURE update_payment_detail();
上述觸發器的問題在于，雖然它成功更新了支付表，但更新的記錄不再出現在payment_detail表中。
例如，如果我按如下方式執行 UPDATE：
UPDATE payment
SET amount = 9.00
WHERE customer_id = 60 AND payment_id = 32034;
的payment_detail表不再顯示如上圖，在表的第一行中為“米爾德里德貝利”的記錄。但是如果我DROP TABLE payment_detail;重新運行表創建，那么記錄就會出現。
我將如何防止這種情況發生？我認為答案植根于我的WHERE條款的條件，但如果是這種情況，我可以使用一些指導。
接下來，是 INSERT 操作的觸發器：
CREATE OR REPLACE FUNCTION insert_payment_detail()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
INSERT INTO payment_detail (customer_id, month_yr, total_amount, payments_count)
SELECT
NEW.customer_id,
to_char(NEW.payment_date, 'Mon YYYY') AS month_yr,
SUM(NEW.amount) AS total_amount,
COUNT(NEW.amount) AS payments_count
FROM customer, payment
WHERE customer.customer_id = NEW.customer_id AND
to_chair(payment.payment_date, 'Mon YYYY') = to_char(NEW.payment_date, 'Mon YYYY');
RETURN NULL;
END;
$$
CREATE TRIGGER insert_payment_detail
AFTER INSERT ON payment
FOR EACH ROW EXECUTE PROCEDURE insert_payment_detail();
如果我按如下方式應用 INSERT：
INSERT INTO payment (customer_id staff_id, rental_id, amount, payment_date)
VALUES (60, 2, 14741, 4.00, now());
我驗證付款表是否保存了新記錄；但是，當我運行時SELECT * FROM payment_detail，它沒有顯示新記錄。所以在我的 UPDATE 和 INSERT 觸發器函式中，目標表只是省略了更新/插入的記錄，直到我洗掉并重新創建表。
我究竟做錯了什么？
uj5u.com熱心網友回復：
更新的觸發器
(a) 您的觸發功能可能有錯誤，請update_payment_detail()嘗試更換
customer_name = first_name || ' ' || last_name
經過
customer_name = NEW.first_name || ' ' || NEW.last_name
(b) 表的列payment_detail是從兩個表customer和復制的payment，因此您可能需要 2 個不同的觸發器函式來進行更新：
CREATE OR REPLACE FUNCTION update_payment_detail()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
UPDATE payment_detail SET
-- customer_id = NEW.customer_id, -- NEW.customer_id is not possible here if it is a column of the table customer; NEW only refers to the columns of the table payment called by the trigger !
-- customer_name = NEW.first_name || ' ' || NEW.last_name, -- idem as above if these columns come from the table customer
month_yr = to_char(NEW.payment_date, 'Mon YYYY')
--FROM
-- customer
WHERE
-- customer.customer_id = NEW.customer_id AND
payment_detail.customer_id = OLD.customer_id ;
RETURN NULL;
END;
$$
CREATE TRIGGER update_payment_detail
AFTER UPDATE
ON payment
FOR EACH ROW
EXECUTE PROCEDURE update_payment_detail();
CREATE OR REPLACE FUNCTION update_customer_detail()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
UPDATE payment_detail SET
customer_id = NEW.customer_id,
customer_name = NEW.first_name || ' ' || NEW.last_name
-- month_yr = to_char(NEW.payment_date, 'Mon YYYY') -- NEW.payment_date is not possible here if it is a column of the table payment; NEW only refers to the columns of the table customer called by the trigger !
--FROM
-- customer
WHERE
customer.customer_id = OLD.customer_id
-- payment_detail.customer_id = OLD.customer_id ;
RETURN NULL;
END;
$$
CREATE TRIGGER update_customer_detail
AFTER UPDATE
ON customer
FOR EACH ROW
EXECUTE PROCEDURE update_customer_detail();
插入觸發器
(c)customer在這個觸發器函式中，對表格的參考聽起來毫無用處。你可以試試這個：
CREATE OR REPLACE FUNCTION insert_payment_detail()
RETURNS TRIGGER LANGUAGE PLPGSQL AS
$$
BEGIN
INSERT INTO payment_detail (customer_id, month_yr, total_amount, payments_count)
SELECT
NEW.customer_id,
to_char(NEW.payment_date, 'Mon YYYY'),
SUM(amount),
COUNT(*)
FROM payment
WHERE customer_id = NEW.customer_id AND
to_char(payment_date, 'Mon YYYY') = to_char(NEW.payment_date, 'Mon YYYY');
RETURN NULL;
END;
$$
CREATE TRIGGER insert_payment_detail
AFTER INSERT ON payment
FOR EACH ROW EXECUTE PROCEDURE insert_payment_detail();
(d) 根據表的主鍵，在表中payment_detail插入兩行payment相同customer_id且to_char(payment_date, 'Mon YYYY')值相同的行時，可能會出現一些沖突。為了防止任何沖突，您可以添加子句ON CONFLICT DO UPDATE ...
(e) 最后，表格payment_detail可以由@Adrian Klaver 提出的視圖替換
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/401953.html
標籤：PostgreSQL的