Re: Delete trigger and data integrity

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,

*Yvonne Zannoun*
Graduate Technical Consultant
Snowflake Software

*Tel: +44 (0) 23 80238 232*

Email: yvonne(dot)zannoun(at)snowflakesoftware(dot)com
Website: www.snowflakesoftware.com
Twitter: @sflakesoftware <http://www,twitter(dot)com(at)sflakesoftware/>
Follow us on LinkedIn <http://www.linkedin.com/company/snowflake-software>

Registered in England & Wales. Registered Number: 4294244
-----------------------------------------------------------------------------------------

On 27 May 2014 11:44, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:

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

--
*Geospatial Technology Company of the Year*
*Read more <http://www.snowflakesoftware.com/2014/05/geospatial-awards/>*