Logging trigger

[Ivan Smahin]

Hello Bart,

Monday, October 15, 2007, 5:18:40 PM, you wrote:

> Hi List, Ruslan

> I am thinking about the feasibility of this:

> inside an after update trigger it would like to do

> Loop over all the fields of a given table comparing the new values  
> with the old values.
> This way I can write to a log table the old values and the new values.
> Is there a way to "read" what fields will be modified? Or do I have  
> to loop over all the field names.

Currently  -  no. Internally we operate with "dirty" fields but it is
not available outside.

> I know I can get all the fields of  
> a table inside a cursor with the SHOW COLUMNS OF Table command. But  
> this feels like a way that would generate way too much overhead. When
> only one field is updated I still would need to loop over all the  
> fields, comparing their old values to the new values.

Yes, it could be much overhead.

> Can I use the name retrieved with the show columns command combined  
> with new (or old) like this

No, you can not use "variable" field names in statements like
NEW.fldName.

> 1) declare cursor = SHOW FIELDS OF table
> 2)How do I get to the name column of this cursor ????

Some short example:

CREATE PROCEDURE sp1()
BEGIN 
       DECLARE a String(20);
       DECLARE  cur1  CURSOR  FOR  SELECT name FROM (SHOW FIELDS FROM t1);
       OPEN cur1;
       BEGIN
           FETCH FIRST cur1 INTO a;
           LOOP
               PRINT a;
               FETCH NEXT cur1 INTO a;
           END LOOP
       EXCEPTION WHEN OTHERS THEN
       END
       CLOSE cur1;
END

> 3) I set local variable thisField = value of current row of cursor 
> (column('name'))
> 4) now I should be able to say IF new.thisField IS NOT EQUAL TO  
> old.thisField then
>         INSERT INTO
> tbl_log(f1,f2,...)VALUES(old.ThisField,new.ThisField,.....)

I see, but it is impossible - it must be identifier.

I  think stored procedures are more powerful way to implement business
logic.

Probably we can add some SQL-function... something like
GetDirtyValueIfChanged( fieldName )


Anyway - add it as feature request to Mantis please.

-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com