insert data into table using loop

Question

I never used loop in a trigger before, I have 3 tables as below:

table 1

  1_pk  |user   |tenant| filter
 ----------------------------------
|   1   | all   |  1   | O 
|   2   | john  |  1   | AA
|   3   | peter |  1   | AB
|   4   | pat   |  1   | AC
|   5   | all   |  2   | O
|   6   | tony  |  2   | AA
|   7   | jim   |  2   | AB

table 2

  2_pk  |table1-FK|role  | tenant
 ----------------------------------
|   1   |   1    |  HR   | 1
|   2   |   2    | staff | 1
|   3   |   2    |  ceo  | 1
|   4   |   5    | staff | 2

table 3

  3_pk  |table1-FK|table2-FK| tenant
----------------------------------
|   1   |    2    |   1     | 1
|   2   |    3    |   1     | 1
|   3   |    4    |   1     | 1
|   4   |    2    |   2     | 1
|   5   |    2    |   3     | 1
|   3   |    6    |   4     | 2
|   4   |    7    |   4     | 2

I want to create a trigger on table 2 so that

When a record is created on table2 with table1_Fk as either 1 or 5(eg: please see table 2) then the trigger has to insert 3 records(if table1_Fk = 1) or 2 records(if table1_Fk = 5) into table 3. in other words, if filter column(table 1) = 'O' then trigger has to insert X number of rows into table 3 for that particular tenant.
When a record is created on table2 with table1_Fk as either 2,3,4,6 or 7 (eg: please see table 2) then the trigger has to insert only 1 records into table 3.

Is there a way that I can use loop in this trigger as I don't know how many records each tenant has in table 1.

Thanks

I don't get your system of when to insert which amount of data into table 3. Can you make your example more concrete and show which values should be inserted? — Philipp
– Philipp, Commented Aug 8, 2016 at 9:34
@Philipp- added more data in table 2 and table 3, hope it explains. Thanks. — Taz
– Taz, Commented Aug 8, 2016 at 9:52

user6638270 · Accepted Answer · 2016-08-08 10:57:29Z

1

You do not need a loop at all. You can do it with a Set-based solution.

CREATE OR REPLACE FUNCTION public.table2ins()
  RETURNS trigger AS
$BODY$ 
declare filt character varying(2);
BEGIN
filt := (SELECT filter FROM table1 WHERE "1_pk" = NEW."table1-FK");
IF filt = 'O' THEN
    INSERT INTO table3 ("table1-FK", "table2-FK", tenant) 
    SELECT table1."1_pk", NEW."2_pk", NEW.tenant FROM
    table1 WHERE table1.tenant = NEW.tenant
    AND table1.filter <> filt;
ELSE
    INSERT INTO table3 ("table1-FK", "table2-FK", tenant) 
    SELECT table1."1_pk", NEW."2_pk", NEW.tenant FROM
    table1 WHERE table1.tenant = NEW.tenant
    AND table1.filter = filt;
END IF; 
RETURN NEW;
END; 
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

IE if the filter is 'O' for all, then the other entries are added, otherwise just the value itself. The advantage of this approach, is that you do not need to know the id values of the 'all' records, you find them by the filter value (you could also use the user field).

BTW I have assumed that the primary key in table3 is a serial.

HTH Jonathan

answered Aug 8, 2016 at 10:57

user6638270

Sign up to request clarification or add additional context in comments.

1 Comment

Taz Over a year ago

Thanks Jonathan: It's working. There is no way I would have expected this solution. Many Thanks.

Collectives™ on Stack Overflow

insert data into table using loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related