Thread: can you do rollback in a trigger function?

can you do rollback in a trigger function?

From
Quang Thoi
Date:

I want to roll back deletion if there is a reference (FK) in another table.

Can I explicitly call a rollback inside a function?

 

 

CREATE OR REPLACE FUNCTION pre_del_prod_proc()

returns trigger as $$

begin

        if exists (select 1 from host_config where config_id = OLD.id) then

            rollback;

        end if;       

end;

$$ language plpqsql;

 

CREATE TRIGGER pre_del_prod_proc

before delete on prod_config

for each row execute procedure pre_del_prod_proc();

 

Thanks,

Quang.

Re: can you do rollback in a trigger function?

From
Vick Khera
Date:

On Tue, Oct 15, 2013 at 2:38 PM, Quang Thoi <Quang_Thoi@symantec.com> wrote:

I want to roll back deletion if there is a reference (FK) in another table.

Can I explicitly call a rollback inside a function?


You should RAISE an error. The transaction should roll back due to the error.

Re: can you do rollback in a trigger function?

From
Shaun Thomas
Date:
On 10/15/2013 01:38 PM, Quang Thoi wrote:

> I want to roll back deletion if there is a reference (FK) in another
> table.
>
> Can I explicitly call a rollback inside a function?

No. Transactions are controlled outside the body of any executing
function. You do have have a couple other options, though. If you can't
use a basic foreign key which does this automatically, you can have your
trigger function return NULL. From the documentation:

"Row-level triggers fired BEFORE can return null to signal the trigger
manager to skip the rest of the operation for this row (i.e., subsequent
triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for
this row)"

You could also raise an explicit error so the user sees something. To
fake a foreign key violation, you'd do:

RAISE EXCEPTION foreign_key_violation;

So you don't need a rollback anyway.

--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email