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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.zannoun@snowflakesoftware.com
Website: www.snowflakesoftware.com
Twitter: @sflakesoftware <http://www,twitter.com@sflakesoftware/&gt;
Follow us on LinkedIn <http://www.linkedin.com/company/snowflake-software&gt;

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

On 27 May 2014 11:44, Albe Laurenz <laurenz.albe@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/&gt;*

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general