Trigger to prevent INSERT/UPDATE/DELETE 1

[flugh]

Scene: 1 Ubuntu (Hoary/5.04) Linux box running PostgreSQL 7.4.7

Tables:
[tt]
invoices
id integer (sequence)
inv_number char(11)

invoice_details
id integer (sequence)
inv_number char(11) FK to invoices.inv_number, required, not null
amount numeric(8,2)
[/tt]
Now, I need to, once getting all the details right, 'lock' this invoice. No deletions, updates, or insertions on the given invoice_number.

I have achieved prevention of update or delete by making a third table:
[tt]
invoice_locks
inv_number char(11) FK to invoices.inv_number, required, not null, on update/delete RESTRICT
[/tt]
This works for update/delete, but I still can't prevent INSERTion of invoice_details using a 'locked' invoice_number. I'm betting a trigger would do it, but having a great deal of trouble getting it right. I could do this with VBA on my Access 2000 front-end, but feel this operation is best left to the database on the back-end.

My skill level with procedures and such is pretty slim. I appreciate any help!

----
JBR

 

[ceco]

you can do it with trigger or with rule, here is a rule example

CREATE OR REPLACE RULE test_rule AS ON INSERT TO invoice_details WHERE NOT EXISTS (SELECT inv_number FROM invoice_locks WHERE inv_number = NEW.inv_number) DO NOTHING;

it is a good idea to put an index

CREATE INDEX invoice_locks_inv_number ON invoice_locks(inv_number);

the trigger is similar (see

http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html),

you should return NULL when the inv_number is present in invoice_locks

 

[flugh]

I'll give this a whirl at the shop this morning. I really appreciate it. I started trying to go at it from the other direction yesterday, making my third table 'edittable' invoices instead of locked ones, but couldn't get the foreign key relationship set up right. Kept refusing to let me delete the invoice number from invoice_edittable because it had related records in invoice_details still. Yeesh.

I think this and the real-life stress is just keeping me from finding that real easy answer. Hopefully your rule will get me there! Will check back this afternoon.

----
JBR

 

[ceco]

the problem with having foreign key to something that must be deleted is solved by defining the foreign key set ON DELETE CASCADE, this helps when the parent key is deleted and then old childs will be delete too, I think this will help in

parent_id integer references par_table(id) on delete cascade,

 

[flugh]

I am with you on the FK. I have one defined in locks referring to the invoice number with ON DELETE RESTRICT to prevent deleting (the first step in rebuilding) a whole invoice. Now, thanks to your push in the right direction, I have a small, simple plpgsql function that works when called as a trigger function.
[tt]
CREATE OR REPLACE FUNCTION test_sett_detail()
RETURNS "trigger" AS
'
BEGIN
IF EXISTS (SELECT sett_number FROM pay.sett_locks WHERE sett_number = NEW.sett_number) THEN
RAISE EXCEPTION \'This settlement is locked\';
END IF;
RETURN NEW;
END;
'
LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER test_sett_detail_trigger
BEFORE INSERT OR UPDATE OR DELETE
ON pay.sett_details
FOR EACH ROW
EXECUTE PROCEDURE pay.test_sett_detail();
[/tt]
Yeah, I switched up from invoice to settlements here, as I need this working by Monday, so the contractor settlements are higher priority. But the schemas are all still the same in principle (settlement->sett_details=>sett_locks).

I got your original RULE suggestion working. However, I'm using Access as the front-end, and the error returned was very vague and not at all related to the real data violation. After not being able to get the RULE to RAISE EXCEPTION (I've gotta figure that's a plpgsql feature, not SQL as used in RULEs), I used the same logic and syntax, along with the example you cited above (plpgsql-trigger.html in my local version of the docs), and got it right. And there was much celebration!! :-D

Thanks a pile! I really appreciate the kick in the right direction!!

----
JBR

 

[flugh]

Ok, the logic on this is broken, as I found out today. If you attempt to DELETE, the function breaks, as there is no NEW defined at the time of DELETE. So I broke up the function (running PostgreSQL 7.4.7, using plpgsql):
[tt]
knbdb=# \df+ test_sett_detail
List of functions
Result data type | Schema | Name | Argument data types | Owner | Language | Source code | Description
------------------+--------+------------------+---------------------+----------+----------+-------------+-------------
"trigger" | public | test_sett_detail | | postgres | plpgsql |
BEGIN
IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
IF EXISTS (SELECT sett_number FROM pay.sett_locks WHERE sett_number = NEW.sett_number) THEN
RAISE EXCEPTION 'This settlement is locked';
END IF;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
IF EXISTS (SELECT sett_number FROM pay.sett_locks WHERE sett_number = OLD.sett_number) THEN
RAISE EXCEPTION 'This settlement is locked';
END IF;
RETURN OLD;
ELSE
RAISE EXCEPTION 'Trigger encountered unknown TG_OP ';
RETURN OLD;
END IF;

END;
|
[/tt]`
HTH anyone trudging up the same mountain of hair-pulling-out that I am ;-)

----
JBR