CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
Anya Chan
Anya Chan

Reputation: 104

Create a trigger after DELETE and update selected statement

I want to create a trigger every time a new row is added to an ArrayA.

This is the code I used:

CREATE TRIGGER increasement
    AFTER INSERT
    ON ArrayA
    FOR EACH ROW
    EXECUTE PROCEDURE func_increasement();

A row is added and I need to update a field in an ArrayB but the problem is that: I need to know the ID of the the new row that has been added in order to know which row to increase in ArrayB.

This is what I've tried:

CREATE OR REPLACE FUNCTION func_increasement()
    RETURNS trigger AS
$$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        UPDATE ArrayB SET number = number + 1 
        WHERE ArrayB.id = ( SELECT ArrayA.arraya_id FROM ArrayA WHERE (SELECT COUNT(*) FROM ArrayA));
END;
$$ LANGUAGE plpgsql;

This is the query that I used for getting the total numbers of the row.

    Select Count(*) from ArrayA;

Upvotes: 1

Views: 53

Answers (2)

vkj
vkj

Reputation: 537

You can use NEW.id which will refer to the currently added row in the table ArrayA

Mysql maintains a reference to the newly added row as NEW in case of Insert query.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31716

I presume you won't need the condition as you only wish to run a trigger after an INSERT event. Also, the initial value in the table ArrayB should either be set to 0 by default or needs a COALESCE during the update. Additionally a Trigger should have a RETURN statement. You may either return NEW or NULL for an AFTER trigger, but it will fail if you use neither.

CREATE OR REPLACE FUNCTION func_increasement()
    RETURNS trigger AS
$$
BEGIN
        UPDATE ArrayB SET number = coalesce(number,0) + 1 
            WHERE ArrayB.id =  NEW.arraya_id;
      RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DEMO

Upvotes: 1

Related Questions