Changeset d715225 for music/triggers/customer_deletion.sql

Timestamp:

05/07/25 11:05:41 (3 weeks ago)

Author:

ManuelTrajcev <manueltrajcev7@…>

Branches:

master

Children:

75ea229

Parents:

d1a8cb0

Message:

deleted customer log trigger

File:

• music/triggers/customer_deletion.sql (modified) (1 diff)

music/triggers/customer_deletion.sql

@@ +1 @@
+DROP TRIGGER IF EXISTS trg_customer_deletion ON customer;
+DROP FUNCTION IF EXISTS customer_deletion();
+
 CREATE OR REPLACE FUNCTION customer_deletion()
 RETURNS TRIGGER AS $$
+DECLARE
+    total_spent NUMERIC(10, 2);
+    invoice_count INTEGER;
 BEGIN
-    INSERT INTO deleted_customer_log (first_name, last_name, deleted_at)
-    VALUES (OLD.first_name, OLD.last_name, NOW());
+    SELECT COALESCE(SUM(total), 0), COUNT(*)
+    INTO total_spent, invoice_count
+    FROM invoice
+    WHERE customer_id = OLD.customer_id;
+
+    INSERT INTO deleted_customer_log (
+        first_name, last_name, deleted_at, total_spent, invoice_count
+    )
+    VALUES (
+        OLD.first_name, OLD.last_name, NOW(), total_spent, invoice_count
+    );
+
     RETURN OLD;
 END;
 $$ LANGUAGE plpgsql;
 
+DROP TRIGGER IF EXISTS trg_customer_deletion ON customer;
+
 CREATE TRIGGER trg_customer_deletion
-AFTER DELETE ON customer
+BEFORE DELETE ON customer
 FOR EACH ROW
 EXECUTE FUNCTION customer_deletion();