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.zann...@snowflakesoftware.com Website: www.snowflakesoftware.com Twitter: @sflakesoftware <http://www,twitter.com@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.a...@wien.gv.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/>*
