Loop in a trigger to get easily all changed fields [CORE2147]

[firebird-automations]

Submitted by: Valdir Marcos (valdir_marcos)

Is duplicated by CORE4924

Votes: 5

Today, in Firebird 2.1.1, is still too hard to code audition logs and/or replication...

It would help a lot if it were possible in a trigger to do something like: ----------------------- Declare Variable i integer; begin for i = 1 to Fields.Length do begin if Fields[i].New <> Fields[i].Old then insert into Log_Table(TableName, UserName, IP_Number, Program_Name, Action, Details) Values ('Table_X', 'User_X', 'IP_X', 'Programa_X', 'Insert or Update or Update/Insert or Delete', 'Changed from ' || Fields[i].Old || ' to ' || Fields[i].New); end; end; -----------------------

It would help a lot if Firebird could provide the variables "Fields" and "Action" (indicating Insert or Update or Update/Insert or Delete for those generic triggers) .

Thanks,

Valdir

[firebird-automations]

Commented by: @cincuranet

You can check updating, deleting, inserting variables to see what action(s) is being performed. For fields look at CORE2131 .

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

I need to point out that it is possible to create a logging/replication trigger using a database procedure which would analyze the table metadata and then create the trigger body and then execute the DML. We do this in our own application today.

While I agree that a mechanism to loop through the table fields, without having to know the fieldnames has some benefits, but a complete solution would require supporting runtime functions which would: return the base datatype for a field, the precision of the field in order for true data logging to occur.

[firebird-automations]

Modified by: @dyemanov

Link: This issue is duplicated by [CORE4924](https://github.com/FirebirdSQL/firebird/issues?q=CORE4924+in%3Atitle) \[ [CORE4924](https://github.com/FirebirdSQL/firebird/issues?q=CORE4924+in%3Atitle) \]