Thread: 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

On 27 May 2014 12:25, Yvonne Zannoun
<yvonne.zannoun@snowflakesoftware.com> wrote:
> CREATE OR REPLACE FUNCTION delete_records()
> RETURNS TRIGGER AS $$
> BEGIN
> delete from "TABLE";
>     RETURN NEW;
> END;
> $$
> LANGUAGE plpgsql;

You can't return NEW in an ON DELETE trigger - there is no NEW record.
Since you're going with a STATEMENT trigger instead, that's not really
relevant anymore (no NEW _or_ OLD record, since statements aren't
necessarily involved with single records), but I thought I'd mention
that slight oversight ;)

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.