Hi,
is it possible to access NEW , OLD rows in an ordinary function
(function which are not TRIGGER PROCEDURES)
motivation:
We need to ensure data integrity of every row in a table.
1. one way is to one or many CHECK constraint on columns (or domains in 7.4).
2. To ADD a trigger procedure that checks for updates and new inserts.
Problem(i feel) with 1 is that as my logic keeps getting more and more
complicated it may not be possible to put everything in a CHECK / set of CHECK
contraints. the logic may even require querying other tables which is anyway
not possible in a CHECK () at the moment.
Problem with 2 is whenever the logic changes we need to check that all the
rows of the existing table satisfies it that logic. for this we either have toreload the data OR run a dummy update on
tableso that procedure is called
for all the existing rows.
3. since ADDing of a CHECK constriaint checks all the rows of a table.
It looks like a better approach to me. whenever the logic changes
we just DROP the CHECK constraint and Add it again so that all the rows are
rechecked.
the problem i am facing with 3 is that i dont seem to find an easy way
of accessing the current record .
two possible workarounds are
1) pass the primary key to the function and query the table again for the record.
2) pass all the columns as arguments to the function that has to check the consistency.
in first case a primary key may not always exists for a relation .
in sencond case too many arguments requried to be passed as ur
logic grows.
so i was wondering abt automagically getting NEW/OLD or a current record
in a function that can be used in check constraint .
Regds
Mallah.
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
