Re: Delete trigger and data integrity

Yvonne 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?

Since the trigger has to run in the same transaction as the INSERT, no
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