Re: help with TCL function

Op 11 Sep 2003 (0:45), schreef ljb <lbayuk@mindspring.com>:

> I think you're missing something. When you use a Tcl function as a
> trigger, any arguments (like $1) are explicitly supplied in the CREATE
> TRIGGER command. You either define the trigger to call the function with
> a constant argument or not; there is no issue of NULL here or an
> optional argument. Show us your CREATE TRIGGER command.

-- This is the function

create or replace function tlow() returns trigger as '
  set NEW($1) [string tolower $NEW($1)]
  return [array get NEW]'
language 'pltcl';

-- Now the table on which I use it

create table bedrijf (
  code varchar unique not null check (code <> ''),
  kvk_nummer varchar,
) with oids;

-- And two triggers

create trigger tlow
  before insert or update
  on bedrijf for each row
  execute procedure tlow('code');

create trigger tlowkvk_nummer
  before insert or update
  on bedrijf for each row
  execute procedure tlow('kvk_nummer');

--

As you can see, the column kvk_nummer is allowed to be null. But /if/
it gets a value, that value should be converted to lowercase. So a sql
statement like this should be allowed:

insert into bedrijf (code) values ('FUBAR');

However this will fail because the trigger tlowkvk_nummer will call
tlow() without an argument. So I want to program tlow() defensively,
after all my DB logic should work, independenlty of anything that goes
on in the frontend.

I think it's a valid thing to expect from a DB or programming language.
Workarounds could be traversing all the frontend code to make sure no
NULL values are used or not using this kind of constraint at all. Not
really options IMO.

Thanks for any help!