Hi,
I wanted to setup a simple "generic" type trigger. What a mean by generic
is that i don't want to hardcode the
NEW.<column> or OLD.<column> calls, i searched for a way to loop over
the NEW/OLD rowtypes but could not figure out how it should be done.
This led me to attempt to do it in a way which i think is
incorrect, first off i extract the column names for the given table
(TG_RELNAME) from the information schema like so
-- extract the column names from tab
FOR colname IN (SELECT column_name FROM information_schema.columns WHERE
table_name = tab) LOOP
colnames[counter] := colname.column_name;
counter := counter + 1;
END LOOP;
Where tab is TG_RELNAME, i then store them in a varchar array (i have
verified that the values contained are correct).
If the TG_OP is 'UPDATE' i then wanted to figure out what column had been
changed (at a later date i might also list the new and old vaules), so the
plan was to do something like this:
ELSIF (TG_OP = 'UPDATE') THEN
FOR i IN 1 .. counter LOOP
IF(NEW.colnames[i] <> OLD.colnames[i])THEN
-- only for testing, to see if a change in a column can be detected
values_changed := values_changed || colnames[i] || ' was changed';
END IF;
END LOOP;
-- values_changed currently contains rubbish
INSERT INTO audit VALUES (audit_id, 'UPDATE', values_changed, '', now(),
TG_RELNAME);
ELSE
The above code does nothing useful and is only to test out the concept. The
problem comes from NEW.colnames[i] because the NEW rowtype does not have a
column called colnames. Can anyone tell me how to append the value of
colnames[i] to NEW so it would work, or is it even possible to iterate of
the NEW/OLD rowtype without having to know the column names ?
Thanks in advance,
Andy
