Create a record automatically in another record when table gets a new record-CodePudding

I have two tables. I want to automatically create a record in the payment record with status false when a record has been added to service table. I would want the payment to look like this for e.g if service has been added

It should create a record in payment automatically like this:

id: autoincrement, fk_service 1, price null, payment_date null, status false

In my web app I will just update the values.

Original tables:

Service

id BIGSERIAL PK,
start_date date not null,
end_date date null,
status VARCHAR(20)

Payment

id BIGSERIAL PK,
fk_service BIGSERIAL,
price DECIMAL,
payment_date date,
status BOOLEAN not null,
FOREIGN KEY(fk_service) references service(id)

Can someone advice, how can I achieve this?

I tried writing a trigger

CREATE FUNCTION insert_payment() RETURNS TRIGGER
AS $insert_payment$
BEGIN
    IF NEW service THEN
        insert into payment(fk_service, status) values (service.id, false) 
    END IF;
END;

I am getting syntax error at or near create line 5 as $BODY$CREATE FUNCTION insert_payment() RETURNS TRIGGER^

CodePudding user response：

You have to "attach" the function into at trigger, which will be listening events on the given table, e.g. an AFTER INSERT trigger

Function

CREATE OR REPLACE FUNCTION insert_payment() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO payment(fk_service, status) values (NEW.id, false);
  RETURN NEW;
END;$$ LANGUAGE plpgsql;

Trigger on table service

CREATE OR REPLACE TRIGGER on_insert_service AFTER INSERT ON service
FOR EACH ROW EXECUTE PROCEDURE insert_payment();

Demo: db<>fiddle