Trigger for conditional insert into table

user_18043204 0
Viewed 82

I have subscription data that is being appended to a table in real-time (via kafka). i have set up a trigger such that once the data is added it is checked for consistency. If checks pass some of the data should be added to other tables (that have master information on the customer profile etc.). The checks function i wrote works fine but i keep getting errors on the function used in the trigger. The function for the trigger is:

CREATE OR REPLACE FUNCTION update_tables()
   RETURNS TRIGGER
   LANGUAGE plpgsql
   AS
$$
BEGIN 
        CASE (SELECT check_incoming_data()) WHEN 0
        THEN INSERT INTO
            sub_master(sub_id, sub_date, customer_id, product_id)
            VALUES(
                (SELECT sub_id::int FROM sub_realtime WHERE CTID = (SELECT MAX(CTID) FROM sub_realtime)),
                (SELECT sub_date::date FROM sub_realtime WHERE CTID = (SELECT MAX(CTID) FROM sub_realtime)), 
                (SELECT customer_id::int FROM sub_realtime WHERE CTID = (SELECT MAX(CTID) FROM sub_realtime)), 
                (SELECT product_id::int FROM sub_realtime WHERE CTID = (SELECT MAX(CTID) FROM sub_realtime))
            );
        RETURN sub_master;
    END CASE;
    
   RETURN sub_master;
END;
$$

The trigger is then:

CREATE TRIGGER incoming_data
   AFTER INSERT
   ON claims_realtime_3
   FOR EACH ROW
       EXECUTE PROCEDURE update_tables();

What I am saying is 'if checks pass then select data from the last added row and add them to the master table'. What is the best way to structure this query?

Thanks a lot!

postgresql sql
1 Answers

The trigger functions are executed for each row and you must use a record type variable called "NEW" which is automatically created by the database in the trigger functions. "NEW" gets only inserted records. For example, I want to insert data to users_log table when inserting records to users table.

CREATE OR REPLACE FUNCTION users_insert()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$
begin
    
    insert into users_log 
    (
        username, 
        first_name, 
        last_name
    )
    select 
        new.username, 
        new.first_name, 
        new.last_name;

    return new;
END;
$function$;

create trigger store_data_to_history_insert 
before insert
on users for each row execute function users_insert();
user_172960840
Related