Real-Life SQL Automation with PostgreSQL Triggers & Procedures

A previous video was focused on SQL triggers, so today I can skip all the explanations and dive right into a hypothetical real-life scenario where we can apply that knowledge.

If you do not understand something, let me know in the comments section. I am always happy to hear from you and help if I can!

========================================

Here are a couple of SQL snapshots from the tutorial:

Our hypothetical scenario for this tutorial is the following:
-- We want to automatically log the time when the invoice is paid

-- Step 1. Create a new table paid_invoices
CREATE TABLE paid_invoices (
id INT GENERATED ALWAYS AS IDENTITY,
invoice_id INT NOT NULL,
customer_name VARCHAR(40) NOT NULL,
paid_on TIMESTAMP NOT NULL
);

-- Step 2. Create a trigger function:
CREATE OR REPLACE FUNCTION log_invoice_paid()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
IF NEW.is_paid [not equal sign, YouTube does not allow it] OLD.is_paid AND NEW.is_paid = 'true'
THEN
INSERT INTO paid_invoices(invoice_id, customer_name, paid_on)
VALUES(OLD.invoice_id, OLD.customer_name, NOW());
END IF;
RETURN NEW;
END;
$$

-- Step 3. Create a trigger that fires the trigger function automatically upon the table update:
CREATE TRIGGER invoice_paid_trigger
BEFORE UPDATE
ON invoices
FOR EACH ROW
EXECUTE PROCEDURE log_invoice_paid();

========================================

Here is a SQL playlist:
https://www.youtube.com/watch?v=oxuSWu_pajg&list=PLzLxFW6Y1L-X53fq9v-RrOT6gIx3xq__S&ab_channel=RuslanBrilenkov
Machine Learning in SQL:
https://youtu.be/oxuSWu_pajg?si=YeW7ubPIELQ7soTz

========================================

A timeline for your convenience:
00:18 Hypothetical Real-Life Scenario
01:05 Step 1: Create a New Table to Log Paid Invoices
01:56 Step 2: Create Trigger Function
04:39 Step 3: Create Trigger
05:09 Check The Trigger!
06:33 How to Drop Trigger?
07:02 Cascade or Restrict options
07:39 Drop Trigger SQl standard VS. PostgreSQL
08:11 Double-check that the trigger is dropped

Leave me a comment. I am always happy to hear from you!

========================================

Ways to connect:
Subscribe!
https://bit.ly/RBrilenkovYT
LinkedIn
https://www.linkedin.com/in/ruslan-brilenkov/
Medium
https://ruslan-brilenkov.medium.com/

========================================

Disclaimer 1: This royalty-free background music was generated with AI software and post-processed afterward. If you are tired of spending too much time and energy finding background music for your videos, dramatically reduce that time with Mix.audio!

Use the promo link below to get a 30% discount on your first monthly subscription payment, and by doing so, you will also support the channel at no extra cost to you:

https://bit.ly/ruslanpromoAI

========================================

Disclaimer 2: everything presented in this video is my own opinion and is meant to educate and share information, nothing mentioned or described here is legal or financial advice. Ruslan Brilenkov is not responsible for any profits or losses associated with your investment. So, please be responsible for your own actions.