Trigger to auto-insert values in a table

Question

Trigger to auto-insert values in a table

Navigation

#1 by (1 votes)

2

I'm new to projecting databases.

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:

Age : 20 with integer type

Date of birth : 12/23/1992 with date type

Religion : Muslim with string type

then each of the 5 will have these 3 attributes.

When a new attribute is inserted in the table users_attributes , I want that in the table users_attributes_values the references to that attribute for each user present are automatically inserted. Creo / 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');

by inserting them, the trigger will insert in users_attributes_value the id of the present users and the id of the attributes just created, Example, there are 3 user 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 you get this result using triggers?

sql java postgresql

asked by Bryan Romero 18.11.2018 в 18:49

source

1 answer

How can I create a query that subtracts a certain percentage? Java - I can not read UDP socket from port / ip

score 1 · Accepted Answer

You can do it with a after insert trigger, which basically makes insert in the%% table% for each user that exists in the users_attributes_values table and places the new attribute in the corresponding field.

On 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();