| 1 | CREATE OR REPLACE FUNCTION trg_fn_log_subscription_status_change()
|
|---|
| 2 | RETURNS TRIGGER LANGUAGE plpgsql AS $$
|
|---|
| 3 | BEGIN
|
|---|
| 4 | IF NEW.status IS DISTINCT FROM OLD.status THEN
|
|---|
| 5 | INSERT INTO subscription_status_history (
|
|---|
| 6 | subscription_id, old_status, new_status,
|
|---|
| 7 | changed_at, changed_by_employee_id, reason
|
|---|
| 8 | ) VALUES (
|
|---|
| 9 | NEW.subscription_id, OLD.status, NEW.status,
|
|---|
| 10 | CURRENT_TIMESTAMP, NULL,
|
|---|
| 11 | 'Automatic log: status changed from ' || OLD.status || ' to ' || NEW.status
|
|---|
| 12 | );
|
|---|
| 13 | END IF;
|
|---|
| 14 | RETURN NEW;
|
|---|
| 15 | END;
|
|---|
| 16 | $$;
|
|---|
| 17 |
|
|---|
| 18 | -- na sekoja promena na subscription status go stava vo status history
|
|---|
| 19 |
|
|---|
| 20 | DROP TRIGGER IF EXISTS trg_log_subscription_status_change ON subscriptions;
|
|---|
| 21 | drop function trg_fn_log_subscription_status_change();
|
|---|
| 22 |
|
|---|
| 23 | CREATE OR REPLACE TRIGGER trg_log_subscription_status_change
|
|---|
| 24 | AFTER UPDATE OF status ON subscriptions
|
|---|
| 25 | FOR EACH ROW
|
|---|
| 26 | EXECUTE FUNCTION trg_fn_log_subscription_status_change();
|
|---|
