CODEWITHSUNDEEP
postgresqlsql-insertdatabase-trigger
Alex
Alex

Reputation: 43

sql trigger - update/insert

one thing I´m struggling with is the definition of triggers in SQL. I need to implement a code for a given table such that: -for any new row inserted in the table, the trigger is activated and the table stores current date on the attribute date. the issue I have with the code included below is that updates all rows in the table, and onle want the inserted table to be updated with the current date.

CREATE FUNCTION func()
RETURNS trigger AS $$
DECLARE
value DATE;
BEGIN
SELECT CURRENT_DATE INTO fecha;
UPDATE tabla SET date = value;
RETURN NULL;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER date
AFTER INSERT ON table
FOR EACH ROW EXECUTE PROCEDURE func();

for any update on any row of the table, the trigger is activated and again, stores the current date on the attribute date. 

CREATE FUNCTION func2()
RETURNS trigger AS $$
DECLARE
value DATE;
BEGIN
SELECT CURRENT_DATE INTO value;
SET NEW.date=value;
RETURN NULL;
END;
$$LANGUAGE plpgsql;

CREATE TRIGGER date
AFTER UPDATE ON table 
FOR EACH ROW
EXECUTE PROCEDURE func2();

Upvotes: 2

Views: 86

Answers (1)

Bergi
Bergi

Reputation: 664528

Use a BEFORE trigger instead that manipulates the row that is getting inserted/updated:

CREATE FUNCTION func()
RETURNS trigger AS $$
BEGIN
  NEW.date = CURRENT_DATE ;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER date
BEFORE INSERT OR UPDATE ON table
FOR EACH ROW EXECUTE PROCEDURE func();

Upvotes: 3

Related Questions