Home / Questions / 214833
Accepted
Bryan Romero
Asked: 2020-11-19 09:49:03 +0800 CST 2020-11-19 09:49:03 +0800 CST

Trigger to auto-insert values ​​in a table

  • 772

I am new to database projection.

I have 3 tables:

users:

+--------+--------+---------+----------+--------+
| userid |  name  | surname | password | admin  |
+--------+--------+---------+----------+--------+
| value1 | value2 | value3  | value4   | value5 |
+--------+--------+---------+----------+--------+

users_attributes:

+--------+--------+--------+
|   id   |  name  |  type  |  
+--------+--------+--------+
| value1 | value2 | value3 |  
+--------+--------+--------+

users_attributes_values(with foreign key userid and attributeid):

+--------+-------------+----------------+
| userid | attributeid | attributevalue |
+--------+-------------+----------------+
| value1 | value2      | value3         |
+--------+-------------+----------------+

The relationship of the users with the attributes is one to many , example: If there are 5 users, and I create 3 attributes:

  1. Age :20 with type integer
  2. Date of birth : 12/23/1992 with date type
  3. Religion : Muslim with string type

so each of the 5 will have these 3 attributes.

When a new attribute is inserted in the users_attributes table , I want the references to that attribute to be automatically inserted in the users_attributes_values ​​table for each user present. I create/Insert 2 attributes:

INSERT INTO users_attributes(
            id, name, type)
    VALUES ('1', 'Peso', 'Int');

INSERT INTO users_attributes(
            id, name, type)
    VALUES ('2', 'Estatura', 'Int');

When inserting them, the trigger will insert in users_attributes_value the id of the users present and the id of the attributes just created, Example, there are 3 users present:

+--------+-------------+----------------+
| userid | attributeid | attributevalue |
+--------+-------------+----------------+
|      1 |           1 |                |
|      1 |           2 |                |
|      2 |           1 |                |
|      2 |           2 |                |
|      3 |           1 |                |
|      3 |           2 |                |
|        |             |                |
+--------+-------------+----------------+

This is the expected result after creating new attributes, how could I get this result using triggers ?

java

1 Answers

  1. Best Answer

    You can do this with an after insert trigger , which basically does an inserton the table users_attributes_valuesfor each user that exists in the table usersand places the new attribute in the corresponding field.

    In the line of:

    CREATE OR REPLACE FUNCTION create_users_attributes_values() RETURNS TRIGGER AS $$
    BEGIN
        --
        -- para cada nueva fila insertada en users_attributes crea las filas
        -- correspondientes en users_attributes_values para todos los 
        -- users existentes en la base de datos
        --
        IF (TG_OP = 'INSERT') THEN
            insert into users_attributes_values (userid, attributeid)
            select userid, NEW.id
              from users;
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER users_attributes_create_users_attributes_values
AFTER INSERT ON users_attributes
    FOR EACH ROW EXECUTE PROCEDURE create_users_attributes_values();
    • 1