On Mar 23, 2006, at 3:33 AM, Steven Brown wrote:
-- On INSERT, fill id from the sequence - creator has UPDATE
permission.
-- Block attempts to force the id.
CREATE OR REPLACE FUNCTION foo_id_insert_procedure() RETURNS
trigger SECURITY DEFINER AS '
BEGIN
IF NEW.id != 0 THEN
RAISE EXCEPTION ''Setting id to a non-default is not allowed'';
ELSE
NEW.id := nextval(''foo_id_seq'');
BTW, with some clever use of TG_RELNAME you could probably make that
function generic, so that you could use it with any table; ie:
NEW.id := nextval( TG_RELNAME || ''_id_seq'');
Same holds true for the update function. You might also want to
create a function that does all the legwork of defining the sequence
and triggers for you.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
