czUMCeA8

1CREATE TABLE IF NOT EXISTS services_elastic (
2    id serial PRIMARY KEY NOT NULL,
3    exact_day varchar(50) NOT NULL,
4    trainer_username varchar(50) NOT NULL,
5    trainer_phone varchar(50) NOT NULL,
6    start_time TIME NOT NULL,
7    end_time TIME NOT NULL,
8    price FLOAT NOT NULL,
9    plan_id INTEGER NOT NULL,
10    service_address_lat NUMERIC(9,6)  NOT NULL,
11    service_address_long NUMERIC(9,6) NOT NULL,
12    service_address_city varchar(50) NOT NULL,
13    service_address_district varchar(50) NOT NULL,
14    service_address_postalcode varchar(50) NOT NULL,
15    limit_number_of_kid INTEGER NOT NULL,
16    current_number INTEGER NOT NULL,
17    provider_email varchar(50) NOT NULL,
18    service_group_types_type_name varchar(50) NOT NULL, 
19    service_groups_name TEXT NOT NULL,
20    service_groups_short_description TEXT NOT NULL
21);
22
23CREATE OR REPLACE FUNCTION process_service_audit() RETURNS TRIGGER AS $services_elastic$
24    BEGIN
25        --
26        -- Create rows in emp_audit to reflect the operations performed on emp,
27        -- making use of the special variable TG_OP to work out the operation.
28        --
29        IF (TG_OP = 'DELETE') THEN
30            INSERT INTO services_elastic
31                SELECT 'D', NOW(), USER, o.* FROM old_table o;
32        ELSIF (TG_OP = 'UPDATE') THEN
33            INSERT INTO services_elastic
34                SELECT 'U', NOW(), USER, n.* FROM new_table n;
35        ELSIF (TG_OP = 'INSERT') THEN
36            INSERT INTO services_elastic(exact_day, trainer_username, trainer_phone, start_time, end_time, price, plan_id, service_address_lat, service_address_long,
37      service_address_city, service_address_district, service_address_postalcode, limit_number_of_kid, current_number, provider_email, service_group_types_type_name, service_groups_name, service_groups_short_description)
38            SELECT scd.exact_day, tr.username, tr.phone, scd.start_time, scd.end_time, ss.total_price, sp.id, sa.lat, sa.long, sa.city, sa.district, sa.postal_code,
39        ss.limit_number_of_kid, ss.current_number, pv.email, sgt.type_name, sg.name, sg.short_description
40                FROM new_table n
41                INNER JOIN trainers tr ON n.trainer_id = tr.id
42                INNER JOIN service_address sa ON n.service_address_id = sa.id
43                INNER JOIN schedule_details scd ON n.time_detail_id = scd.id
44                INNER JOIN service_schedule AS ss ON scd.service_schedule_id = ss.id
45                INNER JOIN service_plans AS sp ON ss.service_plan_id = sp.id
46                INNER JOIN service_groups AS sg ON sp.service_groups_id = sg.id
47                INNER JOIN providers pv ON sg.provider_id = pv.id
48                INNER JOIN service_group_types sgt ON sg.sgroup_type_id = sgt.id;
49        END IF;
50        RETURN NULL; -- result is ignored since this is an AFTER trigger
51    END;
52$services_elastic$ LANGUAGE plpgsql;
53 
54CREATE TRIGGER process_service_audit_insert
55    AFTER INSERT ON service_details
56    REFERENCING NEW TABLE AS new_table
57    FOR EACH STATEMENT EXECUTE FUNCTION process_service_audit();