Thank you very much, that answers my question.
And yes, I think you are right with the FOR EACH ROW/STATEMENT, I didn't think that through for this example.
Thank you for your help!
Kind regards,
Graduate Technical Consultant
Snowflake Software
Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------
On 27 May 2014 11:44, Albe Laurenz <laurenz.albe@xxxxxxxxxx> wrote:
Since the trigger has to run in the same transaction as the INSERT, noYvonne Zannoun wrote:
> I have this question regarding delete triggers and how it affects data integrity.
> So here goes: I have this trigger which deletes everything before I insert new rows.
>
> CREATE OR REPLACE FUNCTION delete_records()
> RETURNS TRIGGER AS $$
> BEGIN
> delete from "TABLE";
> RETURN NEW;
> END;
> $$
> LANGUAGE plpgsql;
>
> CREATE TRIGGER delete_on_insert
> BEFORE INSERT ON "TABLE"
> FOR EACH ROW EXECUTE PROCEDURE delete_records();
>
> My question is what happens while this function is executed? Is there any chance the table can return
> empty data between the delete and insert commands? Or does a trigger like this block this possibility
> somehow?
concurrent transaction will be able to see the "dirty" state between
the DELETE and the INSERT.
Are you sure that you want the trigger FOR EACH ROW and not FOR EACH STATEMENT?
If the INSERT statement inserts more than one row, the trigger will run multiple
times and you will end up with only one row in the table.
Yours,
Laurenz Albe
Geospatial Technology Company of the Year
