CODEWITHSUNDEEP
sqlpostgresqltriggers
user6727547
user6727547

Reputation:

Trigger error in postgreSQL

I am attempting to create a trigger which will delete rows when a certain where clause criteria is met but it throws out an error. What am I doing wrong?

CREATE TRIGGER unknowns
AFTER INSERT
ON  "Amazon".salesdatapcr
FOR EACH ROW 
EXECUTE PROCEDURE delete_my_rows();


CREATE OR REPLACE FUNCTION delete_my_rows()
 RETURNS trigger AS
 $BODY$
 BEGIN
     DELETE FROM "Amazon".salesdatapcr WHERE "Builder" = 'unknown';
     RETURN NEW;
END;   
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Upvotes: 2

Views: 2696

Answers (1)

Joe Taras
Joe Taras

Reputation: 15399

You must encapsulate your DELETE statement in a function, so the trigger will become:

CREATE TRIGGER unknowns
    AFTER INSERT
    ON  "Amazon".salesdatapcr
    FOR EACH ROW 
    EXECUTE PROCEDURE delete_my_rows();

So you must create a function as follow:

CREATE OR REPLACE FUNCTION delete_my_rows()
     RETURNS trigger AS
     $BODY$
     BEGIN

         DELETE FROM "Amazon".salesdatapcr WHERE "Builder" = 'unknown';
         RETURN NEW;
    END;

HERE you can find a brief guide on trigger creation

Upvotes: 1

Related Questions