Thread: trigger problem

trigger problem

From
"Horst Herb"
Date:
I need a function that calculates a check sum on all attributes of a newly inserted column of virtually all of my now
300+tables.
 

Below an example how I do it now; although my pltcl function "trigfunc_crc" is generic, that is it works for any table
wthoutmodification as long as the table has the attribute "crc", it is still awkward to recreate 300 trigger functions
callingthis function whenever I modify trigfunc_crc().
 

1.) Any plans to change this odd behaviour of postgres? (that is, this need for recreating all triggers after modifying
thetrigger function)?
 

2.) Anybody who knows a more efficient way than the rather slow pltcl function below?

3.) anybody who knows a way to avoid writing the trigger for each individual table? (like a CREATE TRIGGER trig_crc
BEFOREINSERT ON * ..."
 

DROP TRIGGER trig_crc ON crclog;
DROP FUNCTION trigfunc_crc();

CREATE FUNCTION trigfunc_crc()
RETURNS OPAQUE as '# create a string by concatenating all field contentsset cstr "";set len [llength $TG_relatts];for
{seti 1}  {$i < $len} {incr i} { set istr [lindex $TG_relatts $i] # skip the crc field! if {[string compare "crc"
$istr]== 0} continue;  # beware of NULL fields if [catch {set cstr $cstr$NEW($istr)}] continue; }  # calculate the
checksumspi_exec "select crc32(''$cstr'') as crcs";# update the new recordset NEW(crc) $crcs;#log the insertspi_exec
"insertinto insertlog(inserted_attributes, crc) values (''$crcs'', "$cstr"))";
 
return [array get NEW]  
' LANGUAGE 'pltcl';   

CREATE TRIGGER trig_crc
BEFORE INSERT OR UPDATE ON crclog
FOR EACH ROW
EXECUTE PROCEDURE trigfunc_crc();