HesNFL5X

1CREATE TABLE IF NOT EXISTS parent_table(
2  id BIGSERIAL PRIMARY KEY,
3  deleted_at TIMESTAMP,
4  created_at TIMESTAMP DEFAULT NOW(),
5  updated_at TIMESTAMP DEFAULT NOW()
6);
7
8CREATE TABLE IF NOT EXISTS child_table(
9  id BIGSERIAL PRIMARY KEY,
10  parent_table_id BIGINT REFERENCES parent_table(id) ON DELETE CASCADE,
11  created_at TIMESTAMP DEFAULT NOW(),
12  updated_at TIMESTAMP DEFAULT NOW()
13);
14
15CREATE OR REPLACE FUNCTION soft_delete()
16 RETURNS TRIGGER AS $$
17 DECLARE
18   command text := ' SET deleted_at = current_timestamp WHERE id = $1';
19 BEGIN
20   EXECUTE 'UPDATE ' || TG_TABLE_NAME || command USING OLD.id;
21   RETURN NULL;
22 END;
23 $$ LANGUAGE 'plpgsql'; 
24
25DROP TRIGGER IF EXISTS soft_delete_parent_table;
26CREATE TRIGGER soft_delete_parent_table BEFORE DELETE ON parent_table FOR EACH ROW EXECUTE PROCEDURE soft_delete();
27
28
29INSERT INTO parent_table VALUES(1);
30INSERT INTO child_table VALUES(1,1);
31
32DELETE FROM parent_table WHERE id = 1;
33-- note that the parent_table row has deleted_at set and the child_table row remains