2

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;
Improve this question
6
  • What is the error you get? Commented Jan 2, 2017 at 14:28
  • Horse, in my ETL I am directing Talend to Drop table if exists and create (I get data weekly). Could it be that it is missing the insert syntax. What can I add to the SQL syntax that will make the trigger fire whenever the table is created in postgreSQL? Commented Jan 2, 2017 at 15:22
  • As long as you don't tell us exactly what the error is you get, there is no way to answer your question. Commented Jan 2, 2017 at 15:23
  • I do not get an error Horse. Thanks to Joe, I was able to compile the above statement. The trigger is not firing that is the problem and at the same time SQL says statement executed. It does not show up under the object explorer in postgreSQL under the table details. Commented Jan 2, 2017 at 15:26
  • But in your question you say "but it throws out an error". Also: "Postgres" has no "object explorer". Which tool do you use to display the triggers? Commented Jan 2, 2017 at 15:27

1 Answer 1

Reset to default
1

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

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

5 Comments

Thanks Joe, the function got executed but the baffling part is when I insert the salesdatapcr table (using ETL) the 'unknown' values still show up in the table. Why do you think that might be?
I am using Talend ETL tool. Is is baffling that even though the function is executed it shows a Zero(0) under Trigger functions. It should show up under the Schema in the database. This is my very first experiment with Triggers. Thanks for the assistance thus far.
Joe, in my ETL I am directing Talend to Drop table if exists and create (I get data weekly). Could it be that it is missing the insert syntax. What can I add to the SQL syntax that will make the trigger fire whenever the table is created in postgreSQL?
But you can truncate table (without delete), so the trigger definition is always present.
Even after changing to truncate table, the unknown builders still show up. select * from "Amazon".salesdatapcr where "Builder" = 'unknown'. Thanks Joe!!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.